# Thread: CountIf with two criteria doesn't count (97/XP)

1. ## CountIf with two criteria doesn't count (97/XP)

Most valuable members of this board, please hear this question...

I have two columns B & C. I want to count all occurences of a value (e.g. 1) in column B where the corresponding value in column C = x.
I use this formula (cell B317 in attached worksheet):
=COUNT(IF(B3:B310=1;IF(G3:G310="x";B3:B310;0);0))
The result remains zero, independent of the number of 'x' present...

It must be something stupid I'm overlooking, but I can't figure out what. Any ideas?

2. ## Re: CountIf with two criteria doesn't count (97/XP

You can use an array formula

<code>=SUM((B2:B309=1)*(C2:C309="x"))</code>

Array formulas need to be entered by using Control+Shift+Enter rather than just Enter.

See attached.

3. ## Re: CountIf with two criteria doesn't count (97/XP)

Tony's formula will work well. An alternative using a "normal" (non-array) formula is:

=SUMPRODUCT((B2:B309=1)*(C2:C309="x"))

4. ## SOLVED Re: CountIf with two criteria doesn't count

Hans, Tony,
Thanks a lot - it works!
Using summing functions for counting - MS logic at it's best <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15>... I'm glad to have you around!

5. ## SOLVED Re: CountIf with two criteria doesn't count

It works because in Excel, TRUE = 1 and FALSE = 0. You can use this in many situations.

6. ## SOLVED Re: CountIf with two criteria doesn't count

Hans...
a star for you... you answered my hidden question <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
Thank you for sharing... this will make it much easier to remember!

#### Posting Permissions

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