COUNT IF (XP / EXCEL)

Topic

New Reply

Your attachment seemed to be empty.
Assuming that you have a bunch of rows with names, and a bunch of columns with attributes such as age and eating preferences
The short answer is that COUNTIF doesn’t do it because it can only handle one condition at a time.

One easy way around this is to create a new column that is equal to your desired condition and count that
e.g. in Z2 the formula =AND(B2=”Apples”,C2=21) would return a result that could be counted.

There are also tricks with array formulae
Finally Pivot Tables are a good general way to do this kind of cross-relationship with ‘database’ data.

Reply | Quote

Replies

Post deleted by jgreggcl

Reply | Quote

A generic approach to multi-conditional counting,

=SUMPRODUCT(–(AgeRange=21),–(FruitRange=”apples”))

Reply | Quote

A generic approach to multi-conditional counting,

=SUMPRODUCT(–(AgeRange=21),–(FruitRange=”apples”))

Reply | Quote

The workbook you attached is completely empty. Please create a workbook with some contents. You can then edit your post (click the button in the post header), tick the ‘Delete attachment’ check box, and specify the path and filename of the corrected workbook. Please do NOT start a new thread for the same question.

Reply | Quote

The workbook you attached is completely empty. Please create a workbook with some contents. You can then edit your post (click the button in the post header), tick the ‘Delete attachment’ check box, and specify the path and filename of the corrected workbook. Please do NOT start a new thread for the same question.

Reply | Quote

One possible formula is

=SUMPRODUCT((B2:B5=21)*(C2:C5=”APPLE”))

Another possibility is

=SUM(IF((B2:B5=21)*(C2:C5=”APPLE”),1,0))

entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter.

Reply | Quote

One possible formula is

=SUMPRODUCT((B2:B5=21)*(C2:C5=”APPLE”))

Another possibility is

=SUM(IF((B2:B5=21)*(C2:C5=”APPLE”),1,0))

entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter.

Reply | Quote

Its best to post to the group so that others can benefit from questions and answers.

Attached is your example extended with a new column, and also with a new sheet holding a pivot table.

I see that others have covered the SUMPRODUCT and Array methods that are also commonly used, so won’t repeat. The choice often depends on your particular needs at the time.

Reply | Quote

Thank you so much

That was exactly what I wanted

Reply | Quote

Please do not delete your posts if there is not a reason to do so, particularly if there are replies to the post. It makes it very difficult for loungers to understand what the message thread is all about.

Reply | Quote

Please do not delete your posts if there is not a reason to do so, particularly if there are replies to the post. It makes it very difficult for loungers to understand what the message thread is all about.

Reply | Quote

Thank you so much

That was exactly what I wanted

Reply | Quote

Its best to post to the group so that others can benefit from questions and answers.

Attached is your example extended with a new column, and also with a new sheet holding a pivot table.

I see that others have covered the SUMPRODUCT and Array methods that are also commonly used, so won’t repeat. The choice often depends on your particular needs at the time.

Reply | Quote