COUNT num of unique IDs in date range with other conditions

[BradleyS]

Excel 2010
I'm struggling to get the correct count of unique IDs in a date range from a table, that includes additional criteria as shown below.
The formula works without the REASON and SOURCE added to the formula, but I need these to define the correct counts.

These are my 2 formulas using Ctrl+Shift+Enter that aren't working:
=SUMPRODUCT(IF((EVENTDATE<=$G$2)*(EVENTDATE>=$F$2), COUNTIFS(EVENTDATE, "<="&$G$2, EVENTDATE, ">="&$F$2, ID, ID,REASON,"Special Risk",SOURCE, "In House"), 0))
=SUMPRODUCT(IF((EVENTDATE<=$G$2)*(EVENTDATE>=$F$2), COUNTIFS(EVENTDATE, "<="&$G$2, EVENTDATE, ">="&$F$2, ID, ID,REASON,"Routine",SOURCE, "In House"), 0))

It is using DNRs of each column header starting from row 2 to row12.
The date range must be within the period shown in F1 and G1. I have shown the dates in the table in red that should not be counted.
The count should show as 2 for both formulas, but is showing as 5 and 7.
What am I doing wrong?

 

[Eric W]

Try this:

Book1
	A	B	C	D	E	F	G
1	ID	Event Date	Reason	Source		Start Date	End Date
2	15764	5/26/2022	Special Risk	In House		5/26/2021	5/26/2022
3	15764	5/26/2022	Special Risk	In House
4	15781	7/15/2021	Special Risk	In House
5	15781	8/17/2005	Routine	In House
6	15781	3/26/2009	Special Risk	Outside	Required Result
7	15781	3/26/2008	Routine	Outside	2
8	22380	6/15/2021	Routine	In House	2
9	22380	6/15/2020	Routine	In House
10	23442	10/27/2021	Routine	In House
11	23442	10/29/2021	Routine	Outside
12	23442	10/29/2021	Routine	In House
Sheet4

Cell Formulas
Range	Formula
E7	E7	=SUMPRODUCT(SIGN(FREQUENCY(IF(($B$2:$B$12>=$F$2)*($B$2:$B$12<=$G$2)*($C$2:$C$12="Special Risk")*($D$2:$D$12="In House"),$A$2:$A$12),ROW(INDIRECT(MIN($A$2:$A$12)&":"&MAX($A$2:$A$12))))))
E8	E8	=SUMPRODUCT(SIGN(FREQUENCY(IF(($B$2:$B$12>=$F$2)*($B$2:$B$12<=$G$2)*($C$2:$C$12="Routine")*($D$2:$D$12="In House"),$A$2:$A$12),ROW(INDIRECT(MIN($A$2:$A$12)&":"&MAX($A$2:$A$12))))))

This assumes the IDs are all numeric under 1,000,000, but it can be adapted if that's not true.

 

[BradleyS]

Well there is no doubt that this works, so thank you very much.
However, I'm going to have to take a hard look at how you have done this as it is using a few things I have never used before i.e. SIGN, and INDIRECT.
Thank you for all your help.

 

[Eric W]

Usually FREQUENCY is the unfamiliar one. Let me know if you have questions.

Anyway, glad I could help! Thanks for the feedback.