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

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

<tbody>
</tbody>

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