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

Options

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

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

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

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • bcrochet
    Options

    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
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • bcrochet
    Options

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

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @bcrochet You bet! Happy to help :)

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • bcrochet
    Options

    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
    Ryan Sides ✭✭✭✭✭✭
    Options

    @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()))

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • bcrochet
    Options

    Thanks again Ryan. Worked perfectly.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @bcrochet Glad to hear! Happy to help.😀

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!