Help... Count Values excluding SUBTOTAL

[santhoshlk]

Dear friends...

I have product range wise random values in column D, and there are subtotals for each ranges in the same column

I need to count the cells where numbers are not equal to zero, but i want to exclude subtotal cells..

how this is possible?

 

[XOR LX]

I think there was needed count of cells which are not 0 and not subtotals and not the sum...

try this:

=SUBTOTAL(2,A1:A8)-COUNTIF(A1:A8,0)

Are you sure? What if all the values for one particular group are zero?

Regards

 

[skorpionkz]

Didn't take it into consideration... it would be much easier if we would know is there is column where is actually saying "Total" or "Subtotal"...

 

[XOR LX]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(1/SUBTOTAL(9,OFFSET(A2,ROW(A2:A9)-ROW(A2),0,1))),1))

This is correct.

 

[skorpionkz]

wow..... thanks skorpi... this is wat i needed... thanks a lot

Careful with this formula, XOR is wright. if there will be any group with all 0 (so your subtotal will be 0) then this formula will fail!