I would like to count how many times an Employee's Name appear for a week (Monday - Saturday)

George Lie

I'm kind of confused here, I want to count how many times a name appeared in a week (Monday - Saturday), according from the date column, for example, how many times Rahmayuni Nasution come between Monday 17 Jan until Saturday 22 Jan, and convert it into column formula

George Lie

Ps. I need the employees to fill in their own attendance from the sheet form. I indexed their salary from the database, I mean to count how many times each name come in a week, in order to summarize their salary

Julio S.

Hi @George Lie,

You may try the following formula that should count on the appearances of each employees between January 17th and 22nd:

=COUNTIFS(EMPLOYEES'NAME:EMPLOYEES'NAME, EMPLOYEES'NAME@row, DATE:DATE, AND(@cell >= DATE(2022, 1, 17), @cell <= DATE(2022, 1, 22)))

This can then be made into a Column formula as needed. You may widen the date range as needed if you need to account for a larger period such as a complete month.

 To build this formula I've used the following functions:

1. COUNTIFS Function.
2. AND Function.
3. DATE Function.

I'd also recommend checking on this article for some helpful tips on how to create efficient formulas that can be more easily processed in Column formulas.

I hope this can be of help. If this doesn't work for you or you'd need further advise, please make sure to share further screenshots of any error provided or the current look of your sheet while making sure that any private/confidential information is hidden. 

Cheers!

Julio