Counting cells filling multiple criteria

    Hoping for some guidance - thought this would be straightforward but cannot solve it. In its most simplistic form I have 3 columns as shown below. I want to know the frequency with which the difference between the value in column A and the corresponding cell (same row) in B is ">5" (A-B), and the corresponding row in column C is "<30". I could of course create another column D [=if(A-B>5,1,0)] then use '=countifs(D:D,1,C:C,"<30") to retrieve the answer but as there will be many columns with different parameters this would require many additional columns needing to be created and will get unwieldy. I have fiddled around with SUMPRODUCT to try and get to the answer but have not had any luck. ANy help would be greatly appreciated. Thanks in advance.


    A B C
    [TABLE="width: 192"]

    [tr]


    [TD="width: 64, align: right"]18[/TD]
    [TD="width: 64, align: right"]9[/TD]
    [TD="width: 64, align: right"]40[/TD]

    [/tr]


    [tr]


    [TD="align: right"]8[/TD]
    [TD="align: right"]4[/TD]
    [TD="align: right"]29[/TD]

    [/tr]


    [tr]


    [TD="align: right"]17[/TD]
    [TD="align: right"]10[/TD]
    [TD="align: right"]41[/TD]

    [/tr]


    [tr]


    [TD="align: right"]19[/TD]
    [TD="align: right"]10[/TD]
    [TD="align: right"]29[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12[/TD]
    [TD="align: right"]10[/TD]
    [TD="align: right"]31[/TD]

    [/tr]


    [tr]


    [TD="align: right"]6[/TD]
    [TD="align: right"]12[/TD]
    [TD="align: right"]29[/TD]

    [/tr]


    [tr]


    [TD="align: right"]11[/TD]
    [TD="align: right"]5[/TD]
    [TD="align: right"]43[/TD]

    [/tr]


    [tr]


    [TD="align: right"]16[/TD]
    [TD="align: right"]8[/TD]
    [TD="align: right"]20[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13[/TD]
    [TD="align: right"]13[/TD]
    [TD="align: right"]33[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13[/TD]
    [TD="align: right"]7[/TD]
    [TD="align: right"]26[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12[/TD]
    [TD="align: right"]6[/TD]
    [TD="align: right"]20[/TD]

    [/tr]


    [tr]


    [TD="align: right"]19[/TD]
    [TD="align: right"]11[/TD]
    [TD="align: right"]20[/TD]

    [/tr]


    [tr]


    [TD="align: right"]11[/TD]
    [TD="align: right"]13[/TD]
    [TD="align: right"]23[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13[/TD]
    [TD="align: right"]4[/TD]
    [TD="align: right"]32[/TD]

    [/tr]


    [tr]


    [TD="align: right"]6[/TD]
    [TD="align: right"]5[/TD]
    [TD="align: right"]24[/TD]

    [/tr]


    [/TABLE]

    Re: Counting cells filling multiple criteria


    Try Sumproduct:


    e.g.


    [COLOR="#0000FF"]=SUMPRODUCT(--(A2:A100-B2:B100>5),--(C2:C100<30))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Counting cells filling multiple criteria


    Hello,


    You could test the Array formula (Control+Shift+Enter)


    Code
    =SUM(((A2:A16)-(B2:B16)>5)*(C2:C16<30))


    or with sumproduct

    Code
    =SUMPRODUCT(((A2:A16)-(B2:B16)>5)*(C2:C16<30))


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Counting cells filling multiple criteria


    Thank you, that worked. Very much appreciated. If the scenario was an "OR" not "AND" (I need both depending on the column), and I wanted to avoid double-counting (eg if both criteria met, avoiding that being counted as 2 events), how would you modify this? The modification below appears to double-counts those rows where both criteria are met. Thanks again.


    =SUMPRODUCT(--(A2:A100-B2:B100>5)+(C2:C100<30))

    Re: Counting cells filling multiple criteria


    As a general rule ... for array formulas :


    AND = *


    OR = +


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Counting cells filling multiple criteria


    Try:


    [COLOR="#0000FF"]=SUMPRODUCT(--((A2:A16-B2:B16>5)+(C2:C16<30)=1))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Counting cells filling multiple criteria


    This works, but I have no idea why...


    =SUMPRODUCT(N((A2:A100-B2:B100>5)+(C2:C100<30)>0))

    Re: Counting cells filling multiple criteria


    Each argument results in TRUEs and FALSEs. When the two arguments are "added" or "multiplied" together it coerces the Trues and Falses to 1's and 0's, respectively.... so in your formula, if the first argument is TRUE you get 1, and if second argument is TRUE you get 1.... adding them together (meaning both args are TRUE) gives you result of 2. And if only one argument is TRUE you get result of 1.


    If you want to know if at least one of the arguments is true, then you use your formula. The greater than 0 check on the combined arguments gives you results of TRUE and FALSE again, so the N() coerces them back to numbers so that SUMPRODUCT can add the results up.


    My last formula would only check that only one of the 2 arguments is true, which, i guess is not what you were looking for.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Counting cells filling multiple criteria


    Sorry one other issue: I have 6 columns (A through F), and need to count the cells when [(A minus B) AND (C minus D)] are both >5, OR [E AND F both <30]. I have tried re-working the above to get there but have been unsuccessful. Thanks.

    Re: Counting cells filling multiple criteria


    Try:


    [COLOR="#0000FF"]=SUMPRODUCT(--(((A1:A100-B1:B100>5)*(C1:C100-D1:D100>5))+((E1:E100<30)*(F1:F100<30)))>0)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login