countifs with equation covering multiple columns

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
125
I need to count the number of roles where either Capital or Expense > 0 (so if Cap+Exp > 0 then count it)

I am having difficulty with the equation portion

[TABLE="width: 401"]
<colgroup><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Role[/TD]
[TD]Capital [/TD]
[TD]Expense[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PM[/TD]
[TD] $ 4,500[/TD]
[TD] $ 2,500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BA[/TD]
[TD] $ - [/TD]
[TD] $ 1,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Testing[/TD]
[TD] $ 10,000[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dev[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PM[/TD]
[TD] $ - [/TD]
[TD] $ 10,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Dev[/TD]
[TD] $ 1,000[/TD]
[TD] $ - [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="colspan: 2"]Expected Results[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]PM[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]BA[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Testing[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Dev[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]If (Capital + Expense) > 0 then count it[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Using your posted example, this regular formula (copied down) seems to return what you're looking for:
Code:
B11: =SUMPRODUCT(($A$2:$A$7=A11)*(($B$2:$B$7+$C$2:$C$7)>0))

edited to add this option...if offsetting values may net to zero:
Code:
B11: =SUMPRODUCT(($A$2:$A$7=A11)*((($B$2:$B$7>0)+($C$2:$C$7>0))>0))
Is that something you can work with?
 
Last edited:
Upvote 0
Using your posted example, this regular formula (copied down) seems to return what you're looking for:
Code:
B11: =SUMPRODUCT(($A$2:$A$7=A11)*(($B$2:$B$7+$C$2:$C$7)>0))

edited to add this option...if offsetting values may net to zero:
Code:
B11: =SUMPRODUCT(($A$2:$A$7=A11)*((($B$2:$B$7>0)+($C$2:$C$7>0))>0))
Is that something you can work with?


Thanks Ron, very helpful. Let me try to take this one step further.

Suppose I have 2 tables like the one above stacked on top of one another, with headers on top of each one. I tried to use the function your provided across both table ranges, but it gave a VALUE error, presumably because of the headers. Is there another way to do this? I have countifs, sumifs available since I am in Excel 2007.

Thanks for your help!
 
Upvote 0
I can't duplicate your experience.
I inserted rows below the first "table" in your example and put a similar table there.
Then I changed the formula references....Correct values were returned.
Can you post a more detailed example?
 
Upvote 0
It appears if you have headings in the range, it gives a #VALUE return, so the only way to achieve the result is to have a SUMPRODUCT for each section and I have many sections. It's not something where I can rearrange the sheet either.

Here is an example:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Role[/TD]
[TD="width: 64"]Cap [/TD]
[TD="width: 64"]Exp[/TD]
[/TR]
[TR]
[TD]Prog[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Prog[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Prog[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Role [/TD]
[TD]Cap [/TD]
[TD]Exp[/TD]
[/TR]
[TR]
[TD]Prog[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I'm still not seeing the issue....With this data in A1:C15
[TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Role
[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Cap
[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Exp
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"][/TD]
[TD="class: xl63, width: 56, bgcolor: white"][/TD]
[TD="class: xl63, width: 56, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Role
[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Cap
[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Exp
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]2
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0
[/TD]
[/TR]
</tbody>[/TABLE]
and this results section in E1:F5

[TABLE="width: 147"]
<tbody>[TR]
[TD]Role
[/TD]
[TD]Results
[/TD]
[/TR]
[TR]
[TD]PM
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]BA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Dev
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Using this formula in F2 (copied down)
Code:
F2: =SUMPRODUCT(($A$1:$A$16=$E2)*((($B$1:$B$16>0)+($C$1:$C$16>0))>0))
I'm getting the above results.

What am I not seeing?
 
Upvote 0
I'm still not seeing the issue....With this data in A1:C15
[TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Role[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Cap[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Exp[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"][/TD]
[TD="class: xl63, width: 56, bgcolor: white"][/TD]
[TD="class: xl63, width: 56, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Role[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Cap[/TD]
[TD="class: xl63, width: 56, bgcolor: white"]Exp[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Prog[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]2[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]PM[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 56, bgcolor: white"]Test[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]1[/TD]
[TD="class: xl64, width: 56, bgcolor: white"]0[/TD]
[/TR]
</tbody>[/TABLE]
and this results section in E1:F5

[TABLE="width: 147"]
<tbody>[TR]
[TD]Role[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Dev[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Using this formula in F2 (copied down)
Code:
F2: =SUMPRODUCT(($A$1:$A$16=$E2)*((($B$1:$B$16>0)+($C$1:$C$16>0))>0))
I'm getting the above results.

What am I not seeing?

I was using the other formula you provided. =SUMPRODUCT(($A$2:$A$15=A19)*(($B$2:$B$15+$C$2:$C$15)>0))
This one returns a #VALUE , the other one works. Do you know why? Perhaps because in this one both operations in column B&C are done in one expression??
 
Upvote 0
In the second formula, each row value was evaluated individually against zero...Not a problem.
But, in the first formula the two row values were summed...then evaluated.
Each time text is added to any other cell, the #VALUE is returned, crippling the results.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top