formula for counting a row with two conditions

Posted by jason on September 05, 2000 1:48 PM

I have a worksheet with text values in columns A and G that vary. Is there a formula that could count how many rows had a certain value in A (say 'apples') that also had a certain value in G (say '4').

Thanks,

Jason

Posted by Ivan Moala on September 06, 0100 1:38 AM

Jason
Try this array formula ie enter with
Ctrl + shift + enter key combination.

=SUM((A1:A8="APPLES")*(G1:G8=4))

Ivan

Posted by Tony Harvey on September 16, 0100 1:59 AM

Hi Ivan,

Could you use this formula to count more than two conditions ? I need up to count up to six individual conditions on a row.

Cheers,

Tony

Posted by Celia on September 16, 0100 5:30 AM

Tony
Yes, as many as you like.
Just use the same stucture as Ivan's formula and add as many criteria as you want. For example :-

=SUM((AI:A10=1)*(B1:B10=2)*(F6:F16="BB")*(etc.....))

The size of each range(number of cells) must be the same and the formula must be entered by Ctrl+Shift+Enter

Celia

Posted by Ivan Moala on September 16, 0100 5:34 AM

Tony
yes you can eg.
=SUM((A1:A8="APPLES")*(G1:G8=4)*(B1:B8=12)* ..etc)

up to the criteria you wish.
If unsure then post your criteria.

Ivan

Posted by Celia on September 16, 0100 5:37 AM

The size of F6:F16 in my example must, of course be the other kind of same size :- F6:F15
Celia

Posted by jason on September 06, 0100 11:46 AM

Success!

Thanks so much, that worked great!

Jason

Posted by Tony Harvey on September 17, 0100 9:21 PM

Thanks for the response Ivan, but it's my fault I should have made the problem
clearer....
In a row from b13:bb13 I have cells with text in them.
BD, BDO, SD1 etc In bc13 I want to count the occurances
of BD & BDO and in bd13 I want to count the occurances
of SD1, L1, L2 etc

So I as you can see the value could occur anywhere
in the row, I just need something similar to the
COUNTA function to record the occurances of each
text string.

Thank you very much for your help, Tony