Counting the # of rows with a value in an excel range

[John MCarthy]

Hi,

I am working with a table of data in excel. Under each column I want to count the number of rows to the left of the column that have at least one cell with a number >0 (greater than 0) in the row.

I have been trying variations of COUNT and COUNTIF and am not having any success - can anyone pls provide a formula for this?

Thanks,

John

 

[GlennUK]

How about something like:
=SUMPRODUCT(--((A3:A32+B3:B32+C3:C32+D3:D32)>0))
... that's if I've understood your question correctly, of course.

 

[John MCarthy]

Hi Glenn,

I tried that on a range that has 4 rows and 10 columns.

Each of the rows has at least one number in it so I would expect an answer of 4, however the answer I got was 7.

What I want the formula to do is pickup each time there is a number in one of the cells in each row and count that as 1.

E.g. if the table has 10 rows and 10 columns, and 6 of the row have numbers (i.e. 4 are blank) the answer would be 6.

Any further suggestions?

Thanks,

John

 

[GlennUK]

Show your formula ... if you'd adjusted my formula as I'd expected the maximum possible result would have been 4.

 

[John MCarthy]

=sumproduct(--((p31:y31+p32:y32+p33:y33+p34:y34)>0))

 

[GlennUK]

That's not what I did. My logic with your data would be:
=sumproduct(--((p3134+q31:q34+r31:r34+s31:s34+t31:t34+u31:u34+v31:v34+w31:w34+x31:x34+y31:y34)>0))

 

[John MCarthy]

Works perfrectly!

Thanks Glenn - you are a legend.

John

 

[barry houdini]

This generic version will work for any range, whatever the size

=SUMPRODUCT((COUNTIF(OFFSET(range,ROW(range)-MIN(ROW(range)),0,1),">0")>0)+0)

so for your example using the range P31:Y34 this will count the number of rows with at least one value greater than zero

=SUMPRODUCT((COUNTIF(OFFSET(P31:Y34,ROW(P31:Y34)-MIN(ROW(P31:Y34)),0,1),">0")>0)+0)

 

[John MCarthy]

Thanks mate!

 

[GlennUK]

This generic version will work for any range, whatever the size

=SUMPRODUCT((COUNTIF(OFFSET(range,ROW(range)-MIN(ROW(range)),0,1),">0")>0)+0)

so for your example using the range P31:Y34 this will count the number of rows with at least one value greater than zero

=SUMPRODUCT((COUNTIF(OFFSET(P31:Y34,ROW(P31:Y34)-MIN(ROW(P31:Y34)),0,1),">0")>0)+0)

That's much more elegant ... top marks.