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

Column D	Column E	Column F
John Doe	yes	1/1/18
John Doe	yes	1/1/18
John Doe	no	1/1/18
John Doe	yes	1/1/18
John Doe	yes	1/3/18
John Doe	yes	1/4/18

<tbody>
</tbody>

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!