Excel formula to count number of rows with value greater than zero.

[ssvv]

Is there a way to count number of rows with values greater than zero?


For example:

Assuming I have selected A1: D10 (note that some rows may not have a value in them). Without creating a 'Total' column, is there a way to find out how many of these 10 rows have a value greater than zero?

Please help. Thanks.

 

[barry houdini]

Do you mean that the total of the row should be more than zero or that it contains any value > 0?

If it's the former then try this formula

=SUMPRODUCT((SUBTOTAL(9,OFFSET(A1:D10,ROW(A1:D10)-ROW(A1),0,1))>0)+0)

 

[ssvv]

Briliant!!! Thank you very much.

The formula works. Can you please explain the formula?

Thanks.