Counting unique values filtered by two other criteria

[tb_OF21]

On worksheet called 'Interventions', column A has a list of reference numbers. Column E is a date and column K is an attendance register.
On a second sheet called 'lists', A1 has a start date and A2 has an end date. On this same sheet, cell M57 shows the text 'attended/completed' as part of a list of options for dropdown on the previous sheet column K.
I want to be able to calculate the number of unique reference numbers in column A when the date in column E is equal to or between the start and end dates (2nd sheet A1 & A2) AND the attendance status matches cell Lists!M57.
I have loads of COUNTIFS formulas in this workbook but I can't figure out a way to count only the unique vales, including not counting blanks. Is this possible?
Thanks

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Eric W]

Assuming my sample data matches your request, try:

Book1
	A				E						K
1	Reference				Date						Status
2	1				1/1/2022						Attended
3	2				1/2/2022						Attended
4	3				1/3/2022						Attended
5	4				1/4/2022						Completed
6	5				1/5/2022						Attended
7	1				1/6/2022						Completed
8	2				1/7/2022						DNS
9	3				1/8/2022						Attended
10	4				1/9/2022						Completed
11	5				1/10/2022						DNS
12	1				1/11/2022						Attended
13	2				1/12/2022						DNS
14	3				1/13/2022						Attended
15	4				1/14/2022						Completed
16	5				1/15/2022						Completed
17	1				1/16/2022						Completed
18	2				1/17/2022						Completed
19	3				1/18/2022						Attended
20	4				1/19/2022						DNS
21	5				1/20/2022						Completed
22
Interventions

Book1
	A												M
1	1/8/2022
2	1/15/2022
3
57													Completed
58
59													Unique count
60													2
Lists

Cell Formulas
Range	Formula
M60	M60	=IFERROR(ROWS(UNIQUE(FILTER(Interventions!$A$2:$A$30,(Interventions!$A$2:$A$30<>"")*(Interventions!$E$2:$E$30>=A1)*(Interventions!$E$2:$E$30<=A2)*(Interventions!$K$2:$K$30=M57)))),0)

 

[tb_OF21]

Assuming my sample data matches your request, try:

Book1
	A				E						K
1	Reference				Date						Status
2	1				1/1/2022						Attended
3	2				1/2/2022						Attended
4	3				1/3/2022						Attended
5	4				1/4/2022						Completed
6	5				1/5/2022						Attended
7	1				1/6/2022						Completed
8	2				1/7/2022						DNS
9	3				1/8/2022						Attended
10	4				1/9/2022						Completed
11	5				1/10/2022						DNS
12	1				1/11/2022						Attended
13	2				1/12/2022						DNS
14	3				1/13/2022						Attended
15	4				1/14/2022						Completed
16	5				1/15/2022						Completed
17	1				1/16/2022						Completed
18	2				1/17/2022						Completed
19	3				1/18/2022						Attended
20	4				1/19/2022						DNS
21	5				1/20/2022						Completed
22
Interventions

Book1
	A												M
1	1/8/2022
2	1/15/2022
3
57													Completed
58
59													Unique count
60													2
Lists

Cell Formulas
Range	Formula
M60	M60	=IFERROR(ROWS(UNIQUE(FILTER(Interventions!$A$2:$A$30,(Interventions!$A$2:$A$30<>"")*(Interventions!$E$2:$E$30>=A1)*(Interventions!$E$2:$E$30<=A2)*(Interventions!$K$2:$K$30=M57)))),0)

You absolute genius thank you!

 

[Eric W]

Glad we could help!