CODEWITHSUNDEEP
excelexcel-formula
Computer_Nerd
Computer_Nerd

Reputation: 102

Counting Unique Records based on Name and Time

I have 3 columns which have a Record, Name, and Time Stamp.

I would like to count all unique records sent by one person for 6 AM.

Here is an example of my table

Name    Record  stamp
ROBERT  589043  6/19/19 6:12 AM
CHARLES 354134  6/19/19 6:21 AM
DAVID   999900  6/19/19 6:40 AM
EVAN    164134  6/19/19 6:43 AM
CHARLES 888800  6/19/19 6:46 AM
EVAN    164134  6/19/19 6:48 AM
DAVID   111254  6/19/19 6:56 AM
A-ROD   454544  6/19/19 6:57 AM
CHARLES 354134  6/19/19 6:59 AM

I would like to see is

Name    Unique Records at 6 AM
ROBERT  1
CHARLES 2
DAVID   2
EVAN    1
A-ROD   1

I used =SUMPRODUCT((HOUR(C1:C9)=6)*(E5:E9="CHARLES"))

But this formula only gives me the 3 times "CHARLES" appeared on my list. How can I incorporate adding a count considering the unique records?

Upvotes: 0

Views: 329

Answers (1)

JvdV
JvdV

Reputation: 75950

Here is a way to do this by formula:

enter image description here

Formula in cell F2:

=SUM(--(FREQUENCY(IF(($A$2:$A$10=E2)*(HOUR($C$2:$C$10)=6),MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1)>0))

Entered through CtrlShiftEnter

But like Ron Rosenfeld mentioned, a Pivot Table is generally more suited for such work.

Upvotes: 2

Related Questions