Count unique dates based on criteria from another column.

[avengedJC]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]JC[/TD]
[TD]22/10/2017[/TD]
[/TR]
[TR]
[TD]JC[/TD]
[TD]22/10/2017[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD]22/10/2017[/TD]
[/TR]
[TR]
[TD]BE[/TD]
[TD]17/10/2017[/TD]
[/TR]
[TR]
[TD]JC[/TD]
[TD]15/10/2015[/TD]
[/TR]
[TR]
[TD]JC[/TD]
[TD]14/10/2017[/TD]
[/TR]
[TR]
[TD]BE[/TD]
[TD]14/10/2017[/TD]
[/TR]
[TR]
[TD]BE[/TD]
[TD]12/10/2017[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD]22/10/2017[/TD]
[/TR]
</tbody>[/TABLE]

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]

maybe something like...

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

 

[avengedJC]

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]

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]

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

 

[avengedJC]

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]

mmmm, the frequency function ignores blanks by design so it shouldn't be counting the blanks in column G

 

[avengedJC]

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]

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[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/22/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/22/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/22/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/17/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/15/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/14/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/14/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/22/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G8[/TH]
[TD="align: left"]=""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(Data!F1:F10="JC",Data!G1:G10),Data!G1:G10),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]