Total COUNTIFs within the last 30 days with two columns of data
Sort of new to this so having some issues.
I'm trying to identify how many entries were made by employees within the last 30 days from a second data sheet. One column has their names and the other has a date entered (both same sheet). I can get the total amount of entries onto a data sheet with this formula:
=COUNTIF({Range}, "Employee Name")
However, when I try to narrow that down to the last 30 days from today's date, I get #UNPARSEABLE. This is what I have for the total formula:
=COUNTIFs({Range}, "Employee Name"), AND({Range}, >TODAY(-30))
Anyone have an idea where I'm off base?
Best Answer
-
@bcrochet Yes! Thanks you for the context. Two ways to do this.
1) On your second sheet, you can use the following formula and replace each name as needed...
=COUNTIFS({AUDITOR NAME Range}, "Ryan", {Created Range}, >TODAY(-30))
You'll need to setup two cross sheet references for this formula. Keep in mind that this is rolling so it's looking for created audits within the last 30 days. (Not per each month. If you need to looka t it by calendar month, you'll need to add a helper column with a formula that pulls the month out of the created date.)
2) You can build a report instead of using your second sheet. See below. The report would display audits created in the last 30 days. You would then group by Auditor and Count by Auditor
Answers
-
@bcrochet Are you trying to get the count by Employee? Like Ryan entered 4, Mary entered 3
Or are you looking for all entries made in the last 30 days regardless of employee name? Like 7 total entries in the last 30 days.
-
Thanks for the reply, Ryan. Some context would help.
Some employees are required to enter 4 'audits' per month. I'm trying to get a rolling count of how many entries each employee has complete in the last 30 days. I have approximately 40 different employees, each doing 4 per month so you can see how it's difficult to see who has completed their audit.
Example below. I have the Auditor name which is the column I use for the range in the formula =COUNTIF({Range}, "Ronnie Jacob") to get all the employee entries for the year. However, I can't figure out a formula to use the date to get an employee count over the last 30-days.
-
@bcrochet Yes! Thanks you for the context. Two ways to do this.
1) On your second sheet, you can use the following formula and replace each name as needed...
=COUNTIFS({AUDITOR NAME Range}, "Ryan", {Created Range}, >TODAY(-30))
You'll need to setup two cross sheet references for this formula. Keep in mind that this is rolling so it's looking for created audits within the last 30 days. (Not per each month. If you need to looka t it by calendar month, you'll need to add a helper column with a formula that pulls the month out of the created date.)
2) You can build a report instead of using your second sheet. See below. The report would display audits created in the last 30 days. You would then group by Auditor and Count by Auditor
-
Awesome....the first one worked perfect. Thanks Ryan!
-
Since you've already helped so much and in case our leadership team ask.
How would I create a formula / helper column to grab just the current month numbers / data entered by person?
-
Thanks again Ryan. Worked perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!