# Thread: COUNT IF (XP / EXCEL)

1. ## Re: COUNT IF (XP / EXCEL)

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.

2. ## Re: COUNT IF (XP / EXCEL)

A generic approach to multi-conditional counting,

=SUMPRODUCT(--(AgeRange=21),--(FruitRange="apples"))

3. ## COUNT IF (XP / EXCEL)

Post deleted by jgreggcl

4. ## Re: COUNT IF (XP / EXCEL)

The workbook you attached is completely empty. Please create a workbook with some contents. You can then edit your post (click the <IMG SRC=http://www.wopr.com/w3timages/edit.gif> 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.

5. ## Re: COUNT IF (XP / EXCEL)

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.

6. ## Re: COUNT IF (XP / EXCEL)

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.

7. ## Re: COUNT IF (XP / EXCEL)

Thank you so much

That was exactly what I wanted

8. ## Re: COUNT IF (XP / EXCEL)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•