# Excel help (counting things conditionally)

**URL:** <https://boards.straightdope.com/t/excel-help-counting-things-conditionally/527157>\
**Category:** Factual Questions\
**Created:** [January 31, 2010, 8:30pm UTC](https://boards.straightdope.com/t/excel-help-counting-things-conditionally/527157 "2010-01-31T20:30:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\
**Post date:** [January 31, 2010, 8:30pm UTC](https://boards.straightdope.com/t/excel-help-counting-things-conditionally/527157/1 "2010-01-31T20:30:20Z")

</div>

I love using you guys as my online help. 🙂

I have a spreadsheet…and I want to count the values in column C, (let’s say C2:C9999), based on 2 conditions: the value in column C is \> 0 and the corresponding value in column K2:K9999 = YES.

Similarly, what if I want to sum the values in column C, instead of just counting them?

I suspect COUNTIF and SUMIF are involved, but I can’t figure out how to do the 2-part logical condition.

---

<div class="post-metadata">

**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\
**Post date:** [January 31, 2010, 9:35pm UTC](https://boards.straightdope.com/t/excel-help-counting-things-conditionally/527157/2 "2010-01-31T21:35:04Z")

</div>

> [@jsc1953](#):
>
> I suspect COUNTIF and SUMIF are involved, but I can’t figure out how to do the 2-part logical condition.

If you’re using Excel v2007, you can try [COUNTIFS()](http://office.microsoft.com/en-us/excel/HA100474941033.aspx) instead of COUNTIF().

If you’re stuck with Excel v2003, you can do contortions with SUMPRODUCT() or CSE array functions with {SUMIF()}

---

<div class="post-metadata">

**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\
**Post date:** [January 31, 2010, 10:00pm UTC](https://boards.straightdope.com/t/excel-help-counting-things-conditionally/527157/3 "2010-01-31T22:00:01Z")

</div>

In Excel 2007, it’s easy: =COUNTIFS(C2:C9999,"\>0",K2:K9999,"=YES").  
In Excel 2003, the easiest way is to just create a third column (which you can hide) that captures the joint condition and then just do the count off of that column.

---

<div class="post-metadata">

**Author:** ![aceplace57](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/aceplace57/32/3500_2.png) [@aceplace57](https://boards.straightdope.com/u/aceplace57)\
**Post date:** [January 31, 2010, 10:03pm UTC](https://boards.straightdope.com/t/excel-help-counting-things-conditionally/527157/4 "2010-01-31T22:03:07Z")

</div>

use a nested if and put the results in a work column  
I put my work columns at the far right, so they are out of my print range

put this in Z1 and copy down to Z999  
=IF(+C1\>0,IF(K1\>0,C1,0),0)

at the bottom of C sum(z1:z999)

Nested if implies an AND condition if C1 \>0 AND k1 \>0 move value in “C” to Z1 Else move 0

---

<div class="post-metadata">

**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\
**Post date:** [January 31, 2010, 10:27pm UTC](https://boards.straightdope.com/t/excel-help-counting-things-conditionally/527157/5 "2010-01-31T22:27:16Z")

</div>

Excel 2003; I came back to report that I’d thought of the work-around with the additional column with a double IF. So it appears unanimous…thanks!