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

[ExcelApprenticeChris]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="width: 74, align: center"][/TD]
[TD="width: 74, align: center"]A
[/TD]
[TD="width: 79, align: center"]B
[/TD]
[TD="width: 56, align: center"]C
[/TD]
[TD="width: 64, align: center"]D
[/TD]
[TD="width: 230, align: center"]E
[/TD]
[TD="width: 230, align: center"]F
[/TD]
[TD="width: 230, align: center"]G
[/TD]
[TD="width: 230, align: center"]H
[/TD]
[TD="width: 230, align: center"]I
[/TD]
[/TR]
[TR]
[TD="width: 74, align: center"]1
[/TD]
[TD="width: 74, align: center"]Type 1[/TD]
[TD="width: 79, align: center"]Type 2[/TD]
[TD="width: 56, align: center"]Type 3[/TD]
[TD="width: 64, align: center"]Type 4[/TD]
[TD="width: 230, align: center"]Category[/TD]
[TD="width: 230, align: center"][/TD]
[TD="width: 230, align: center"]Categories:[/TD]
[TD="width: 230, align: center"][/TD]
[TD="width: 230, align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]200000[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"][/TD]
[TD="align: center"]p[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Q
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]26000[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"][/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]400000[/TD]
[TD="align: center"]300000[/TD]
[TD="align: center"]40000[/TD]
[TD="align: center"]425000[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]75000[/TD]
[TD="align: center"]100000[/TD]
[TD="align: center"]175000[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10000[/TD]
[TD="align: center"]350000[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]120000[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]160000[/TD]
[TD="align: center"]50000[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]18000[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12300[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Marcelo Branco]

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]

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.

 

[Marcelo Branco]

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.

You are welcome.

You can find an excellent explanation about SUMPRODUCT at
http://xldynamic.com/source/xld.SUMPRODUCT.html

M.

 

[ExcelApprenticeChris]

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]

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.