Countifs + unique values

[ItalianPlatinum]

Hello - is someone able to assist me in modifying/providing a formula or VBA to accomplish the below. very simply trying to count column A7 down for unique values (no duplicates) based off Column B7 down being today's date. My current formula only accomplishes the first part.

test2.xlsm
	A	B	C
1		10/3/2020
2
3	Total	13
4	DESIRED	10
5
6	Type	Submission Date
7	AAA	10/3/2020
8	BBB	10/3/2020
9	III	10/3/2020
10	JJJ	10/3/2020
11	KKK	10/3/2020
12	LLL	10/3/2020
13	MMM	10/3/2020
14	NNN	10/3/2020
15	OOO	10/3/2020
16	PPP	10/3/2020
17	ZZZ	9/29/2020	Not current Day
18	XXX	9/29/2020	Not current Day
19	YYY	9/29/2020	Not current Day
20	OOO	9/30/2020	Duplicate
21	OOO	9/30/2020	Duplicate
22	OOO	9/30/2020	Duplicate
23	PPP	9/30/2020	Duplicate
Ns

Cell Formulas
Range	Formula
B1,B7:B16	B1	=TODAY()
B3	B3	=SUMPRODUCT((Ns!$A$7:$A$2995<>"")/COUNTIFS(Ns!$A$7:$A$2995,Ns!$A$7:$A$2995&""))

 

[jasonb75]

This works, there may be other ways as well

Book1
	A	B	C
1		04/10/2020
2
3	Total	10
4	DESIRED	10
5
6	Type	Submission Date
7	AAA	04/10/2020
8	BBB	04/10/2020
9	III	04/10/2020
10	JJJ	04/10/2020
11	KKK	04/10/2020
12	LLL	04/10/2020
13	MMM	04/10/2020
14	NNN	04/10/2020
15	OOO	04/10/2020
16	PPP	04/10/2020
17	ZZZ	29/09/2020	Not current Day
18	XXX	29/09/2020	Not current Day
19	YYY	29/09/2020	Not current Day
20	OOO	30/09/2020	Duplicate
21	OOO	30/09/2020	Duplicate
22	OOO	30/09/2020	Duplicate
23	PPP	30/09/2020	Duplicate
Ns

Cell Formulas
Range	Formula
B1,B7:B16	B1	=TODAY()
B3	B3	=SUMPRODUCT(IFERROR((Ns!$B$7:$B$2995=B1)/COUNTIFS($B$7:$B$2995,$B$1,Ns!$A$7:$A$2995,Ns!$A$7:$A$2995&""),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ItalianPlatinum]

This works, there may be other ways as well

Book1
	A	B	C
1		04/10/2020
2
3	Total	10
4	DESIRED	10
5
6	Type	Submission Date
7	AAA	04/10/2020
8	BBB	04/10/2020
9	III	04/10/2020
10	JJJ	04/10/2020
11	KKK	04/10/2020
12	LLL	04/10/2020
13	MMM	04/10/2020
14	NNN	04/10/2020
15	OOO	04/10/2020
16	PPP	04/10/2020
17	ZZZ	29/09/2020	Not current Day
18	XXX	29/09/2020	Not current Day
19	YYY	29/09/2020	Not current Day
20	OOO	30/09/2020	Duplicate
21	OOO	30/09/2020	Duplicate
22	OOO	30/09/2020	Duplicate
23	PPP	30/09/2020	Duplicate
Ns

Cell Formulas
Range	Formula
B1,B7:B16	B1	=TODAY()
B3	B3	=SUMPRODUCT(IFERROR((Ns!$B$7:$B$2995=B1)/COUNTIFS($B$7:$B$2995,$B$1,Ns!$A$7:$A$2995,Ns!$A$7:$A$2995&""),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Nice! Thank you. So far this is working as I would like.