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:

	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.

 

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