Is there a formula to Count Unique values with Criteria and between two dates

Question

I need help to count all UNIQUE each Serial # of each Phase # between two dates. Raw data changes every day. I know there is a way to formulate this but I can't do it.

I tried Cleaning the raw data with Remove Duplicates of "Serial #" and "Phase #" and using countif formula between range but it gives me an incorrect count vs manual counting/filtering.

Serial #	Phase #	Date
TKP-45160	7400000	02-Jan-24
SIF-99993	7400000	03-Jan-24
MXP-78670	7400000	09-Jan-24
TKP-45160	7400000	14-Jan-24
IGB-61886	7400001	09-Jan-24
UGZ-18592	7400001	12-Jan-24
UKI-16823	7400001	16-Jan-24
LEI-46548	7400001	17-Jan-24
VEN-90058	7400002	04-Jan-24
VEN-90058	7400002	16-Jan-24
VEN-90058	7400002	22-Jan-24
BQV-31917	7400003	16-Jan-24
HZU-76576	7400003	19-Jan-24
YCF-16393	7400005	02-Jan-24
IQW-54705	7400005	11-Jan-24
CJZ-61175	7400005	12-Jan-24
BYS-13223	7400006	04-Jan-24
SPI-15979	7400006	08-Jan-24
HUU-10645	7400006	10-Jan-24
BYS-13223	7400006	12-Jan-24
BYS-13223	7400006	14-Jan-24
REP-36224	7400006	16-Jan-24
WKG-85714	7400007	04-Jan-24
VIN-86315	7400007	06-Jan-24
VZM-12692	7400007	07-Jan-24
DKX-32255	7400007	18-Jan-24
OTJ-13529	7400007	21-Jan-24

Answer 1

Try the following formula using SUM() or SUMPRODUCT() function along with IF() with COUNTIFS()

Using SUM() function one would require to hit the CTRL+SHIFT+ENTER while using SUMPRODUCT() it won't require.

---

• Formula used in cell I4

=SUMPRODUCT(IF(($F4=$C$4:$C$30)*($D$4:$D$30>=$G4)*($D$4:$D$30<=$H4),
  1/COUNTIFS($C$4:$C$30,F4,$B$4:$B$30,$B$4:$B$30,$D$4:$D$30,">="&$G4,$D$4:$D$30,"<="&$H4),0))

---

Formula needs to fill down!

---

Another alternative using FREQUENCY() function:

---

=SUMPRODUCT(N(IFERROR(IF(($F4=$C$4:$C$30)*($D$4:$D$30>=$G4)*($D$4:$D$30<=$H4),
 FREQUENCY(MATCH($B$4:$B$30&"|"&($F4=$C$4:$C$30)*($D$4:$D$30>=$G4)*($D$4:$D$30<=$H4),
 $B$4:$B$30&"|"&($F4=$C$4:$C$30)*($D$4:$D$30>=$G4)*($D$4:$D$30<=$H4),0),
 (ROW($B$4:$B$30)-ROW($B$4)+1))>0,0),0)))

---

The above has been explained here:

Count the unique Values that met the Criterias (without using Pivot and Unique() function)

---