SUBTOTAL Function Anomaly

[wdgor]

I have a spreadsheet (Office 365 version) that has 3000 rows. Column D has currency numbers, i.e., with 2 decimal places. Column B has Parent Account names. I need to apply a filter in the Parent Accounts column and correctly count the non-zero values in column D, i.e., get a subtotal count. Here is my formula: ="Count: "&SUBTOTAL(102,D4:D3000)&CHAR(10)&" "&DOLLAR(SUBTOTAL(9,D4:D3000)). The interesting thing about the execution of this formula is that is gives the right value IF a filter is applied. If there is no filter, it gives a wrong number. Here is what I mean:

With no filter: (the count should be 140)

Count: 2997 $139,716,993.65

With a filter: (this is the right count based on the filtered Parent)

Count: 4 $3,422,629.27

I can't figure out why it is not counting properly when no filter is applied. Thre are either $ numbers in each row or "0" all the way to row 3000. Any help from the geniuses that watch this site would be appreciated. Thanks in advance.

 

[Anonymous1378]

There's nothing anomalous about the subtotal function; it ignores any rows that are not included due to a filter. Nothing in your formula defines your desired parent account, so when you unfilter your range, it counts everything. COUNT() counts numbers, and 0 is most certainly a number.

 

[wdgor]

The formula is not using COUNT(). It is only using SUBTOTAL. The formula is in column D and the filter is in Column B. Shouldn’t SUBTOTAL option 102 Ignore the 0s and only count the numbers greater than 0? If that is not true, then I probably have the formula wrong. Thanks.

 

[wdgor]

Is there a way to have SUBTOTAL IGNORE 0s? If not, is there some other way that I could have a SUBTOTAL- like function that would work similarly? My issue is that I need to have the Count right even if there is no filter applied, i.e. ignore the 0s. I thought that the 192 option would do that. Thanks again.

 

[Peter_SSs]

The formula is not using COUNT().

Not the actual function COUNT, but SUBTOTAL(102, .. does COUNT the visible numbers. If 0 is visible it will be counted since it is a number.

Try this instead for the count

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D4,ROW(D4:D3000)-ROW(D4),0,1)),--(D4:D3000>0))

 

[wdgor]

That worked perfectly!! Thanks so much. Also, thanks for the explanation - it does make sense. Perhaps Microsoft can augment the SUBTOTAL function to address some options to include or exclude zeros.

 

[Peter_SSs]

You're welcome. Glad it helped.

 

[wdgor]

That did work well for positive numbers. I have more columns than the column D in the request and some of them have negative numbers. The formula doesn't count the negatives. Is there a way to count either positive or negative numbers but not count 0. Thanks.

 

[wdgor]

This formula modification allows the SUBTOTAL to count all the positive and negative numbers but eliminate the 0s.

="Count: " & SUMPRODUCT(SUBTOTAL(2, OFFSET(D4, ROW(D4:D3000)-ROW(D4), 0, 1)), --(ABS(D4:D3000) > 0)) & CHAR(10) & " " & DOLLAR(SUBTOTAL(9, D4:D3000))

 

[Peter_SSs]

I have more columns than the column D in the request and some of them have negative numbers. The formula doesn't count the negatives. Is there a way to count either positive or negative numbers but not count 0. Thanks.

Just change this part --(D4:D3000<>0)