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

ABC
1RoleCapital Expense
2PM $ 4,500 $ 2,500
3BA $ - $ 1,000
4Testing $ 10,000 $ -
5Dev $ - $ -
6PM $ - $ 10,000
7Dev $ 1,000 $ -
8
9
10Expected Results
11PM2
12BA1
13Testing1
14Dev1
If (Capital + Expense) > 0 then count it

<colgroup><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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:

RoleCap Exp
Prog12
PM00
Test10
PM10
Test10
Prog10
Prog01
Role Cap Exp
Prog00
PM20
Test00
PM03
Test10

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
I'm still not seeing the issue....With this data in A1:C15
Role
Cap
Exp
Prog
1
2
PM
0
0
Test
1
0
PM
1
0
Test
1
0
Prog
1
0
Prog
0
1
Role
Cap
Exp
Prog
0
0
PM
2
0
Test
0
0
PM
0
3
Test
1
0

<tbody>
</tbody>
and this results section in E1:F5

Role
Results
PM
3
BA
Test
3
Dev

<tbody>
</tbody>

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
RoleCapExp
Prog12
PM00
Test10
PM10
Test10
Prog10
Prog01
RoleCapExp
Prog00
PM20
Test00
PM03
Test10

<tbody>
</tbody>
and this results section in E1:F5

RoleResults
PM3
BA
Test3
Dev

<tbody>
</tbody>

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,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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