Count of Distinct Values in complex worksheet

[Concertshots73]

Hi there! I am stumped and need some assistance. I have a worksheet that is used to determine metrics for accounting. Essentially, it lets me know which accounts and invoices are due for follow up today. Up to now, I have been filtering on the "next" date for values year to date, which gives me the list of invoices. Then copying the data into a separate worksheet to create a pivot table which gives me the dollars per account and then I can get the sum of all amounts, and a count of distinct account numbers. I would then go back to the main data and just highlight the invoice column and use the count at the bottom of the screen to find out total number of invoices. Lots of manual work.

I am converting over to a quicker format and I have figured out how to do two of the 3 needed formulas, but I am stuck on the last one. I need to figure out how to count the distinct values of account #s after the date filter is applied.

**NOTE: I cannot add additional columns to the existing format as it is set in stone.

Trying to add a shot of the sheet, But I can't figure out how, so this was the best I could do
[TABLE="width: 822"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Acct #
[/TD]
[TD]Customer Name
[/TD]
[TD]Invoice #
[/TD]
[TD] Amount
[/TD]
[TD]Last
[/TD]
[TD]Next
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]6316793
[/TD]
[TD]ABC Company
[/TD]
[TD]35597124
[/TD]
[TD]3,672.50
[/TD]
[TD]3/31/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]6331645
[/TD]
[TD]Main Street Bakery
[/TD]
[TD]35716014
[/TD]
[TD]450.26
[/TD]
[TD]4/7/2014
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]6332832
[/TD]
[TD]Canada Maple Syrup
[/TD]
[TD]35739826
[/TD]
[TD]1,073.50
[/TD]
[TD]3/29/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35789788
[/TD]
[TD]4,250.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35789789
[/TD]
[TD]500.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35794847
[/TD]
[TD]2,700.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35794848
[/TD]
[TD]1,700.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35810923
[/TD]
[TD]7,750.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35813366
[/TD]
[TD]350.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35820109
[/TD]
[TD]1,700.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]6336166
[/TD]
[TD]Kilt Makers
[/TD]
[TD]35784377
[/TD]
[TD]3,500.00
[/TD]
[TD]3/27/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35823350
[/TD]
[TD]500.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]6320491
[/TD]
[TD]Blinking Light Warehouse
[/TD]
[TD]35819368
[/TD]
[TD]1,950.00
[/TD]
[TD]4/7/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]6320491
[/TD]
[TD]Blinking Light Warehouse
[/TD]
[TD]35829007
[/TD]
[TD]1,950.00
[/TD]
[TD]4/7/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]557215
[/TD]
[TD]Keyboard Central
[/TD]
[TD]35844656
[/TD]
[TD]1,700.00
[/TD]
[TD]4/5/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]6320491
[/TD]
[TD]Blinking Light Warehouse
[/TD]
[TD]35833848
[/TD]
[TD]3,220.00
[/TD]
[TD]4/7/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]6339992
[/TD]
[TD]Orange Juicers
[/TD]
[TD]35845469
[/TD]
[TD]3,900.00
[/TD]
[TD]3/30/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]6329319
[/TD]
[TD]Closet Organizers
[/TD]
[TD]50012044
[/TD]
[TD]550.00
[/TD]
[TD]3/24/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]6329319
[/TD]
[TD]Closet Organizers
[/TD]
[TD]50012044
[/TD]
[TD]550.00
[/TD]
[TD]3/24/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]6329319
[/TD]
[TD]Closet Organizers
[/TD]
[TD]50012044
[/TD]
[TD]550.00
[/TD]
[TD]3/24/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]6329319
[/TD]
[TD]Closet Organizers
[/TD]
[TD]50012044
[/TD]
[TD]550.00
[/TD]
[TD]3/24/2017
[/TD]
[TD]5/1/2017
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]6329319
[/TD]
[TD]Closet Organizers
[/TD]
[TD]50012044
[/TD]
[TD]550.00
[/TD]
[TD]3/24/2017
[/TD]
[TD]11/1/2016
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Next (F:F) is essentially a schleduled follow up date
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Filter "Next" date for values less than or equal to today
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1. Sum of Amount
[/TD]
[TD]24,170.26
[/TD]
[TD="colspan: 2"]SUMIF(F:F,"<="&TODAY(),D:D)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2. Count of Invoice #
[/TD]
[TD]11
[/TD]
[TD="colspan: 2"]COUNTIF(F:F,"<="&TODAY())
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3. Count of distinct account #
[/TD]
[TD]<<<<<------argh!
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any insight you can give on the proper formula to get me what I need is appreciated

 

[Jonker]

Try this:

=SUM(IF(FREQUENCY(A:A,A:A)>0,1))

 

[Jonker]

Nvm... thats for all values, try this one instead:

=SUM(--(FREQUENCY(IF(F2:F23<=TODAY(),MATCH(A2:A23,A2:A23,0)),ROW(A2:A23)-ROW(A2)+1)>0))

use control + shift + enter