I would like to count how many times an Employee's Name appear for a week (Monday - Saturday)
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
Answers
-
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
-
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:
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!