Count distinct values in Excel with multiple criteria (strings)-CodePudding

I have an excel sheet with a large table with 3 main columns (Forward, Reverse and Species). I need to count distinct species for each pair from Forward and Reverse columns but I can't seem to make it work. I've tried going around it but the closest thing I found was this: "=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0))", which comes from this website

CodePudding user response：

Well, this is the formula, you need to use to get the Sum of Distinct Species

Formula used in cell I21

=SUMPRODUCT(IF(($A$2:$A$27=$H21)*($B$2:$B$27=I$20),
1/COUNTIFS($D$2:$D$27,$D$2:$D$27,$A$2:$A$27,$A$2:$A$27,
$B$2:$B$27,$B$2:$B$27),0))

And Fill Down!