I have a list of permit numbers in column A and the dates they were issued in column B. These columns are filtered with the intent of using the filter to see how many permits were issued in a particular day, month or any given time period.
Above column B I have the following formula
=SUMPRODUCT((B9:B2576<>"")/COUNTIF(B9:B2576,B9:B2576&""))
It shows 92. This is now many distinct dates there are in the column. For example the date 1/5/15 appears 63 times. This formula will count that date only once.
The problem is that when I filter for 1/5/15 this formula doesn't subtotal column B along with the sumproduct function. If it did the amount shown when filtering for 1/5/15 in column B would be "1".
I've uploaded the file. Can someone please assist with the formula above column B so that it does the sumproduct function as it's currently doing along with a subtotal function?
Thanks so much!
Above column B I have the following formula
=SUMPRODUCT((B9:B2576<>"")/COUNTIF(B9:B2576,B9:B2576&""))
It shows 92. This is now many distinct dates there are in the column. For example the date 1/5/15 appears 63 times. This formula will count that date only once.
The problem is that when I filter for 1/5/15 this formula doesn't subtotal column B along with the sumproduct function. If it did the amount shown when filtering for 1/5/15 in column B would be "1".
I've uploaded the file. Can someone please assist with the formula above column B so that it does the sumproduct function as it's currently doing along with a subtotal function?
Thanks so much!