CountIF unique dates with multiple criteria with

[JAnders]

Hello excel experts,

I'm trying to figure out a way to have a countif formula that will capture number of unique dates with multiple criteria.

Column D = Names
Column E = Description
Column F = Date

Cell A2 = "John Doe"
Cell A3= "Yes"
Cell A4 = Formula to return the number of unique days "John Doe" (cell A2) is listed with column E being "Yes" (cell A3). In the example below it should return 3

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]yes[/TD]
[TD]1/1/18[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]yes[/TD]
[TD]1/1/18[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]no[/TD]
[TD]1/1/18[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]yes[/TD]
[TD]1/1/18[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]yes[/TD]
[TD]1/3/18[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]yes[/TD]
[TD]1/4/18[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully that makes sense

Thank you

 

[Eric W]

Try:

=SUM(SIGN(FREQUENCY(IF(D2:D10=A2,IF(E2:E10=A3,F2:F10)),F2:F10)))

and confirm it by pressing Control+Shift+Enter after you enter it in the formula bar, not just enter.

 

[JAnders]

Thank you Eric, that worked perfectly !

I do have a follow up question though. How would I change it be unique dates > a certain date (that is listed in cell A1)

 

[Eric W]

That would be:

=SUM(SIGN(FREQUENCY(IF(D2:D10=A2,IF(E2:E10=A3,IF(F2:F10>A1,F2:F10))),F2:F10)))
with Control+Shift+Enter.

Glad to help!