# Another Excel question: counting cells if they meet multiple conditions

**URL:** <https://boards.straightdope.com/t/another-excel-question-counting-cells-if-they-meet-multiple-conditions/304072>\
**Category:** Factual Questions\
**Created:** [May 16, 2005, 3:12pm UTC](https://boards.straightdope.com/t/another-excel-question-counting-cells-if-they-meet-multiple-conditions/304072 "2005-05-16T15:12:00Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![audiobottle](https://avatars.discourse-cdn.com/v4/letter/a/bc8723/32.png) [@audiobottle](https://boards.straightdope.com/u/audiobottle)\
**Post date:** [May 16, 2005, 3:12pm UTC](https://boards.straightdope.com/t/another-excel-question-counting-cells-if-they-meet-multiple-conditions/304072/1 "2005-05-16T15:12:00Z")

</div>

I’ve been stuck on this for a little while. Here’s my example:

A B  
blah blah  
blip blah  
blah blip  
bleck blah  
blah blah

I only want to count the number of “blah blah” pairs. Is there a way I can count the number of instances of A=something AND B=something? I tried:

=sum(and(countif(A1:A5,“blah”),countif(B1:B5=“blah”))), but that doesn’t work, unsurprisingly.

---

<div class="post-metadata">

**Author:** ![jack12](https://avatars.discourse-cdn.com/v4/letter/j/dbc845/32.png) [@jack12](https://boards.straightdope.com/u/jack12)\
**Post date:** [May 16, 2005, 3:34pm UTC](https://boards.straightdope.com/t/another-excel-question-counting-cells-if-they-meet-multiple-conditions/304072/2 "2005-05-16T15:34:59Z")

</div>

The easiest way to do this is to create a column c with an ‘if’ formula in, scoring either 1 if true or 0 if false, and then simply sum that column. If you’re looking to count every instance or column A = column B, then create column c and enter the formula:

=IF(A2=B2,1,0)

If you exclusively want to count the instances of A = blah and B = blah, enter the formula:

=IF(AND(A2=“blah”,B2=“blah”),1,0)

Then (in either case) just sum the contents of column C.

---

<div class="post-metadata">

**Author:** ![audiobottle](https://avatars.discourse-cdn.com/v4/letter/a/bc8723/32.png) [@audiobottle](https://boards.straightdope.com/u/audiobottle)\
**Post date:** [May 16, 2005, 3:59pm UTC](https://boards.straightdope.com/t/another-excel-question-counting-cells-if-they-meet-multiple-conditions/304072/3 "2005-05-16T15:59:48Z")

</div>

Yeah, I thought about that solution. I was hoping there was something else I could do instead though.

---

<div class="post-metadata">

**Author:** ![AllShookDown](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@AllShookDown](https://boards.straightdope.com/u/AllShookDown)\
**Post date:** [May 16, 2005, 5:04pm UTC](https://boards.straightdope.com/t/another-excel-question-counting-cells-if-they-meet-multiple-conditions/304072/4 "2005-05-16T17:04:22Z")

</div>

This seems to work:

=SUMPRODUCT((A1:A15=“blah”)\*(B1:B15=“blah”))

---

<div class="post-metadata">

**Author:** ![audiobottle](https://avatars.discourse-cdn.com/v4/letter/a/bc8723/32.png) [@audiobottle](https://boards.straightdope.com/u/audiobottle)\
**Post date:** [May 16, 2005, 7:47pm UTC](https://boards.straightdope.com/t/another-excel-question-counting-cells-if-they-meet-multiple-conditions/304072/5 "2005-05-16T19:47:03Z")

</div>

> [@AllShookDown](#):
>
> This seems to work:
> 
> =SUMPRODUCT((A1:A15=“blah”)\*(B1:B15=“blah”))

You, sir, are a genius.