Count - Attendance Formula

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.

Re: Count - Attendance Formula

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

Re: Count - Attendance Formula

Edited the layout for you

Re: Count - Attendance Formula

Thank you.

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.