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

Joined
Mar 17, 2013
Messages
9
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 1Type 2Type 3Type 4CategoryCategories:
2
000200000Pp13
3
0000Q
Q02
4
26000000RR12
5
40000030000040000425000P
1
6
075000100000175000P1
7
0010000350000Q1
8
120000016000050000R1
9
180000123000Q1
10
0000R0

<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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,995
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Apr 29, 2013
Messages
773
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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.
 
Joined
Mar 17, 2013
Messages
9
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
Joined
Aug 23, 2010
Messages
16,995
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,686
Members
425,230
Latest member
DzOus

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
Top