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.

 

[wdgor]

Correct. That allowed it to count all the positive and negative numbers but not count the 0. Thank you for your original solution - I just played around with adding a small piece.