Count unique by date/time stamp

[Anne Troy]

Sample file: http://annetroy.com/mrexcel/Sample2.xlsx

Countif and Sumif have always been my nemesis.

I need to count how many unique airlines are listed in column G, each time the timestamp changes in column A.

I only need it to show once in the column.

Sorting first is fine, if necessary. A helper column is fine, too, on a hidden worksheet.

I thank you soooooo much!!!!

Oh... and I also need to check if the timestamp is between 8 pm and 8 am (the next day). A Y or N would be fine. I didn't finish my comments in the file.

 

[Aladin Akyurek]

For 1/22/2018 4:00:00 PM, you have 2 unique (different) airlines active. Is that what you are after?

 

[Anne Troy]

Yes, Aladin, thanks!

I'd like to have the airline count in the first row of each new timestamp.

 

[Aladin Akyurek]

Yes, Aladin, thanks!

I'd like to have the airline count in the first row of each new timestamp.

In M2 control+shift+enter, not just enter, and copy down:

=IF(ISNA(MATCH($A2,$A$1:A1,0)),SUM(IF(FREQUENCY(IF(1-($G$2:$G$51=""),IF($A$2:$A$51=$A2,MATCH($G$2:$G$51,$G$2:$G$51,0))),ROW($G$2:$G$51)-ROW($G$2)+1),1)),"")

 

[Anne Troy]

That works GREAT. Now, I just need to subtract one from it, but keep getting a #VALUE error.
Still trying...

 

[Aladin Akyurek]

That works GREAT. Now, I just need to subtract one from it, but keep getting a #VALUE error.
Still trying...

Try: Contro+shift+enter and copy down

=IF(ISNA(MATCH($A2,$A$1:A1,0)),SUM(IF(FREQUENCY(IF(1-($G$2:$G$51=""),IF($A$2:$A$51=$A2,MATCH($G$2:$G$51,$G$2:$G$51,0))),ROW($G$2:$G$51)-ROW($G$2)+1),1))-1,"")​

 

[Anne Troy]

Oh, I did do the CSE. Checking out the -1 you gave. :D

 

[Anne Troy]

Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes Yes yes yes.

THANKS!!!!

 

[Aladin Akyurek]

Anne, you are welcome.