How to make relation counting in Excel?

Question

I have a table with employees, id, name, date and time they attend and leave like this:

What I need now is to make a new table based on the old table to count how many employee attended on each date. I did something using an if statement like this:

=IF(B2=total!C:C;COUNT(total!D2:D154)) 

But sometimes it doesn't work.

Please, does anyone have any ideas?

Answer 1

Assuming that one employee can attend only once a day (I see you only have one line for one employee), the following formula might count how many employees attended on some particular day:

=COUNTIFS(C:C,C1,D:D,">0")

In my data, the column A holds ID numbers; B - names; C - dates; D - time when they attended, or no value (the cell is left blank) if the employee did not attend. If I used your screenshot data, the cell C1 would represent the value of 02/09/2013.

COUNTIFS is used to count values which meet several conditions. In our case we have 2 conditions.
First, the part C:C,C1 checks how many dates like in C1 there is in total in the column C:C.
Then the part D:D,">0" counts how many cells in the column D:D are not left blank (employees actually attended).

If both conditions on the same row are satisfied, then the row is counted, otherwise it's not counted. The formula processes every row in the sheet, as we have defined so in our conditions: C:C and D:D.