Counting Unique Values in a Date Range

[speth]

Hello,

I have an access report that's exported in CSV. The report tells me whenever a person uses an HID card to gain access to a door.

I'm running a long term report, and want it to count unique visitors on days.

ColA is the time (m/d/y hh:mm:ss AM), ColB is time, ColC is the Person, ColD is the Portal, ColE is status (let in or denied).

So what I'm trying to do essentially is say, "On XYZ date, how many UNIQUE names are recorded".

Any help? I was trying to wrap my brain around a COUNTIFS, but I don't think that's the right approach.

 

[speth]

Sorry, and just to add - the system records any time someone enters. So if they go out to lunch and come back, they're recorded twice. That's the reasoning for needing it to look up unique entries for a day and not just count all entries.

 

[CyrusTheVirus]

Hi speth,

Do you have ID numbers as well? If so that would be the preferred reference, because some people can have the same name...

Does the date matter or are you wanting to count unique names for the whole list? If the date matters, then please paste a sample of your spreadsheet so we can see the exact date format.

Excel Workbook
	A	B	C	D	E	F	G
1	Time	Time	Person	Portal	Status	*	Unique Count
2	xx	xx	Bob	xx	xx	*	6
3	xx	xx	Susie	xx	xx	*	*
4	xx	xx	Bob	xx	xx	*	*
5	xx	xx	Andrew	xx	xx	*	*
6	xx	xx	Rebecca	xx	xx	*	*
7	xx	xx	Rebecca	xx	xx	*	*
8	xx	xx	Rebecca	xx	xx	*	*
9	xx	xx	Sam	xx	xx	*	*
10	xx	xx	Dan	xx	xx	*	*
Sheet4

 

[speth]

Thanks Cyrus!

No ID numbers, but the names are LAST, FIRST so shouldn't be anything erroneous.

Your formula is counting unique names, but I need to count unique names for a certain day. The sheet has ~2900 rows of information over the course of 90 days or so. The question I'm being asked is "How many people use the door on any given day?"

I changed the range in your formula and it threw out a DIV/0 error - I have a few rows with blank information. Is that the problem?

 

[CyrusTheVirus]

speth,

Can you please please paste a sample of your spreadsheet so we can see the exact date format?