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?

 

[santhoshlk]

hi... this above sumproduct formaula counts including subtotals.... pls help

 

[XOR LX]

You still haven't clarified whether the cells with subtotals begin with the text "Sub" or you just said that because they contain the formula =SUBTOTAL(... which begins with "SUB".

Big difference.

Regards

 

[skorpionkz]

You still haven't clarified whether the cells with subtotals begin with the text "Sub" or you just said that because they contain the formula =SUBTOTAL(... which begins with "SUB".

Big difference.

Regards

Additionally to this, if you have in the column next to it saying "Total" or "Subtotal"? so you can see by your self where our totals are.

 

[Aladin Akyurek]

Re: Help... Count Values excluding 0

iam using office 2003 and they are actual subtotal formulas

6
0
5
Sub 11
2
3
0
Sub 5

Result for count cells value <>0 excluding subtotals should be 4

Either, control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(1/A2:A100),1))

Or, just enter:

=SUM(COUNTIF(A2:A100,{"<0",">0"}))

assuming that the Sub cells contain the Sub string.

 

[skorpionkz]

Re: Help... Count Values excluding 0

=SUM(COUNTIF(A2:A100,{"<0",">0"}))

assuming that the Sub cells contain the Sub string.

This is what we are trying to clarify for a while already

 

[santhoshlk]

You still haven't clarified whether the cells with subtotals begin with the text "Sub" or you just said that because they contain the formula =SUBTOTAL(... which begins with "SUB".

Big difference.

Regards

yes... my cell contains the formula strating with =subtotal, not text

 

[XOR LX]

yes... my cell contains the formula strating with =subtotal, not text

=SUMIF(A2:A100,"<>0")/2

Regards

 

[skorpionkz]

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)

 

[Aladin Akyurek]

yes... my cell contains the formula strating with =subtotal, not text

Control+shift+enter, not just enter:

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

 

[santhoshlk]

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)

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