# Count unique dates based on criteria from another column.

#### avengedJC

##### New Member
I have two sheets, one named, "DATA" and another named, "STATS".

I need to enter a formula in a cell in the "STATS" sheet that will count the number of unique dates in column G from the "DATA" sheet but ONLY IF column F contains the text, "JC".

 F G JC 22/10/2017 JC 22/10/2017 AF 22/10/2017 BE 17/10/2017 JC 15/10/2015 JC 14/10/2017 BE 14/10/2017 BE 12/10/2017 AF 22/10/2017

<tbody>
</tbody>

So the formula should have the outcome of showing 3. as there are 3 unique dates that also have "JC" in column F.

Thanks in advance for you help. I have tried other formulas from other posts on this site and others but just can't seem to get to the correct outcome that I want.

#### Weazel

##### Well-known Member
maybe something like...

=SUM(IF(FREQUENCY(IF(Data!F1:F9="JC",Data!G1:G9),Data!G1:G9),1)) Control Shift Enter

#### avengedJC

##### New Member
Thanks for getting back to me so quickly...

Hmmm... that seems to giving me a value of "1". Not sure why it's not correct, sorry, I do not yet understand complex formulas like this! Could the fact that I have a header on the "DATA" sheet be effecting the result?

#### avengedJC

##### New Member
AH! Thanks... sorry the dataset I had was much larger than just the 10 cells so I edited the formula to include the whole column. I'ts no working, thank you so much!

#### Vinoth_Shob

##### New Member
Select both the data and remove duplicates then use Countifs function.

#### avengedJC

##### New Member
maybe something like...

=SUM(IF(FREQUENCY(IF(Data!F1:F9="JC",Data!G1:G9),Data!G1:G9),1)) Control Shift Enter
Sorry, another problem - some of the fields in the date column are left blank... how can I edit this formula so that it doesn't count the blank cells as a unique entry also?

#### Weazel

##### Well-known Member
mmmm, the frequency function ignores blanks by design so it shouldn't be counting the blanks in column G

#### avengedJC

##### New Member
mmmm, the frequency function ignores blanks by design so it shouldn't be counting the blanks in column G
Strange. I came to the conclusion that was the issue because when I filled the blank cells it seemingly counts correctly... it counts one less. Therefore I presumed it was counting blanks as a unique value.

#### Weazel

##### Well-known Member
I'm not sure but I'm not able to replicate the issue here...

this is with both an actual blank as well as NULL value...

You can read up on the function here...https://support.office.com/en-us/article/FREQUENCY-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9

Unknown
 F G H I 1 JC 10/22/2017 2 JC 10/22/2017 3 AF 10/22/2017 3 4 BE 10/17/2017 5 JC 10/15/2015 6 JC 10/14/2017 7 JC 10/14/2017 8 JC 9 AF 10/22/2017 10 JC

<tbody>
</tbody>
Data

Worksheet Formulas
CellFormula
G8=""

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
I3{=SUM(IF(FREQUENCY(IF(Data!F1:F10="JC",Data!G1:G10),Data!G1:G10),1))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>