Count unique dates based on criteria from another column.

avengedJC

New Member
Joined
Jul 21, 2017
Messages
11
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".

FG
JC22/10/2017
JC22/10/2017
AF22/10/2017
BE17/10/2017
JC15/10/2015
JC14/10/2017
BE14/10/2017
BE12/10/2017
AF22/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
Joined
Dec 24, 2011
Messages
3,155
maybe something like...

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

avengedJC

New Member
Joined
Jul 21, 2017
Messages
11
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
Joined
Jul 21, 2017
Messages
11
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
Joined
Dec 11, 2017
Messages
30
Select both the data and remove duplicates then use Countifs function.
 

avengedJC

New Member
Joined
Jul 21, 2017
Messages
11
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
Joined
Dec 24, 2011
Messages
3,155
mmmm, the frequency function ignores blanks by design so it shouldn't be counting the blanks in column G
 

avengedJC

New Member
Joined
Jul 21, 2017
Messages
11
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
Joined
Dec 24, 2011
Messages
3,155
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
FGHI
1JC10/22/2017
2JC10/22/2017
3AF10/22/20173
4BE10/17/2017
5JC10/15/2015
6JC10/14/2017
7JC10/14/2017
8JC
9AF10/22/2017
10JC

<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>
 

Forum statistics

Threads
1,082,575
Messages
5,366,415
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top