countifs with equation covering multiple columns

S

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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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
Ron Coderre said:
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?
Click to expand...


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
Ron Coderre said:
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?
Click to expand...

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
You must log in or register to reply here.

Similar threads

G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
562
Greenbehindthecells
G
M
  • Question Question
Modified code not working, as expected
2
Replies
15
Views
2K
Mr2017
M
willow1985
  • Question Question
Trouble with Countifs formula. Is there significance to where you reference your data?
Replies
7
Views
1K
Fluff
Fluff
B
  • Question Question
Count number of months per year between two dates
Replies
1
Views
984
DanteAmor
DanteAmor
G
  • Question Question
sumifs with two criterias in two columns
Replies
2
Views
621
go2bobby
G

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

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