# Count - Attendance Formula

• Hello All,

Im looking to count the total number of days a person has entered a facility. In this example you will see that each individual has entered more than once each day. The end result should be as follows. I've been working on this for awhile and just cant seem to get this no matter the type of formula or combination of formulas I use.

Sample Table

[TABLE="width: 275"]

[tr]

[td]

Transaction

[/td]

[td]

Date

[/td]

[td]

Person

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/5/16

[/td]

[td]

DOE , JANE

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/5/16

[/td]

[td]

DOE , JANE

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/4/16

[/td]

[td]

DOE , JANE

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/4/16

[/td]

[td]

DOE , JANE

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/4/16

[/td]

[td]

DOE , JANE

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/21/16

[/td]

[td]

SMITH , JOHN

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/7/16

[/td]

[td]

SMITH , JOHN

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/7/16

[/td]

[td]

SMITH , JOHN

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/17/16

[/td]

[td]

SMITH , JOHN

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/17/16

[/td]

[td]

SMITH , JOHN

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

10/12/16

[/td]

[td]

SMITH , JOHN

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/28/16

[/td]

[td]

WILLIAMS , JAMES

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/28/16

[/td]

[td]

WILLIAMS , JAMES

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/28/16

[/td]

[td]

WILLIAMS , JAMES

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/18/16

[/td]

[td]

WILLIAMS , JAMES

[/td]

[/tr]

[tr]

[td]

Entered

[/td]

[td]

11/18/16

[/td]

[td]

WILLIAMS , JAMES

[/td]

[/tr]

[/TABLE]

Jane Doe - 2
John Smith - 4
James Williams - 2

## Files

• Re: Count - Attendance Formula

There are several ways of doing this, but none of them are going to work. Why? Because of the format of the dates in column B, all of which are going to be counted as unique because of this format: 10/5/2016 10:09:18 Once you have got rid of the time stamp in that column, this is one way of doing it:

=SUMPRODUCT(IFERROR((1/COUNTIF(\$B\$2:\$B\$17,\$B\$2:\$B\$17))*(\$C\$2:\$C\$17=E2),0))

confirmed by pressing CTRL+SHIFT+ENTER (not just ENTER) so that curly brackets {} appear around the formula (don't type these yourself, as it won't work - if you enter it correctly, they will appear automatically).

E2 contains the name of the person, which must match exactly what is in column C.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Count - Attendance Formula

Apologies for the layout of my response - the forum is ignoring line breaks this morning!

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Count - Attendance Formula

Edited the layout for you

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: Count - Attendance Formula

Thank you.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Count - Attendance Formula

Another option, albeit not a formula:

Add a helper column adjacent to the data table and use it to extract the date using the formula =INT(B2), copied down the column.
Use Advanced Filter to copy unique values to another range.

In your example worksheet, assuming helper column is D (with a header of Date2), initiated Advanced Filter, select the range of cells (C1:D17), tick the option to copy to another location and tick the box "Unique records only").

You will end up with a list of unique entries and can then use a Pivot Table to get the counts.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!