# Counting number of rows to multiple criteria when the sum of a row range is greater than 0.

#### ExcelApprenticeChris

##### New Member
Hello,
Thank you for taking time to read my query/challenge?? I have been having a mental blank for the last two evenings on this and decided to ask you wise people for help.

The question summary is at the end. Sorry it is long, I have just tried to add as much detail as possible.

I have the following arrangement of data:

 A B C D E F G H I 1 Type 1 Type 2 Type 3 Type 4 Category Categories: 2 0 0 0 200000 P p 1 3 3 0 0 0 0 Q Q 0 2 4 26000 0 0 0 R R 1 2 5 400000 300000 40000 425000 P 1 6 0 75000 100000 175000 P 1 7 0 0 10000 350000 Q 1 8 120000 0 160000 50000 R 1 9 18000 0 12300 0 Q 1 10 0 0 0 0 R 0

<tbody>
</tbody>

I have been trying to build a formula in cell J2, for example, that says count how many times G2 appears in the range E2:E10 when the sum of each row is greater than 0. For example P would return 3 because there are 3 instances where in the range E2:E10 P appears and each row sums to greater than 0.

The formula in J3 that is looking up G3 (i.e. Q ) on the other hand would return 2 and not 3 because although there are 3 instances of Q appearing in the range E2:E10, only twice does the row in which it appears sums to greater than 0 because in row A3:D3 where the Q in E3 appears, it sums to 0.

I have tried various SUMIFS, COUNTIFS and SUMPRODUCT and IF formulas exhausting my knowledge but frustratingly I could only count every time it appeared the categories in G appeared in the range on column E. So I always over counted because P returns as the desired 3 but Q and R for example returns as the 3 and 3 respectively and not the 2 and 2 expected.

I have found a temporary workaround using simple IF((SUM and COUNTIF by putting a formula in column H2:H4 of the following form: =IF((SUM(A2,B2,C2,D2))=0,0,1) which simply returns a 1 if the sum of the row for columns Type 1 to 4 is greater than 0 and it returns a 0 if equal to 0. So as expected cell H3 and H10 are zero and the rest are 1.

This is then coupled with the formula in I2:I4 of the form: =COUNTIFS(E2:E10,G2,H2:H10,"1") which counts the number of times for example G2 (i.e. P) appears in the range E2:E10 when its corresponding cell in the range H2:H10 is 1 i.e. it is adding up to more than 0 in the row of the type 1-4 columns.

So my question is can anyone put this operation (these two formula) into a single formula that removes the need to have the columns H and I, so i can just have a single string in J2:J4.

I have tried all sorts of SUMPRODUCT, SUMIFS and COUNTIFS combinations but at a mental block trying to get the correct solution.

Can anyone create a formula that counts the amount of times a category in column G appears in the range E2:E10 when its corresponding SUM of the rows - for the 'type' columns - add up to more than 0.

Hopefully it is not too much of a challenge!

Anyway if you made it this far, thank you very much for your help. I really appreciate your time. Thank you.

Christopher

PS if you need any more detail please do not hesitate to ask.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Marcelo Branco

##### MrExcel MVP
Try this

J2
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(\$A\$2:\$D\$10,ROW(\$A\$2:\$D\$10)-ROW(\$A\$2),0,1))>0),--(\$E\$2:\$E\$10=G2))
copy down to J4

Hope this helps

M.

#### Drrellik

##### Well-known Member
Thank you for that, I learned from it. still learning about the (--( I see from time to time here in the forum but again thanks for that. Don. I am sure that will help Chris.

#### ExcelApprenticeChris

##### New Member
Hello Marcelo,

Thank you kindly for your help. I have spent a while studying your solution and have been looking up SUBTOTAL and OFFSET as I have never used them before now. Your solution works perfectly, thank you

#### Marcelo Branco

##### MrExcel MVP
Hello Marcelo,

Thank you kindly for your help. I have spent a while studying your solution and have been looking up SUBTOTAL and OFFSET as I have never used them before now. Your solution works perfectly, thank you

You are welcome and thanks for the feedback.

M.

Replies
3
Views
286
Replies
4
Views
240
Replies
1
Views
350
Replies
2
Views
163
Replies
0
Views
119

1,186,808
Messages
5,959,933
Members
438,455
Latest member
Beverly Jarrell

### 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.

### Which adblocker are you using?

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

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