Total COUNTIFs within the last 30 days with two columns of data

bcrochet

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?

Ryan Sides

@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

Ryan Sides

@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.

bcrochet

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.

Ryan Sides

@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

bcrochet

Awesome....the first one worked perfect. Thanks Ryan!

Ryan Sides

@bcrochet You bet! Happy to help :)

bcrochet

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?

Ryan Sides

@bcrochet You can use this to get the count for those entered in the current month...

=COUNTIFS({AUDITOR NAME Range}, "Ryan", {Created Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

bcrochet

Thanks again Ryan. Worked perfectly.

Ryan Sides

@bcrochet Glad to hear! Happy to help.😀