Date use with COUNTIFS formula (Non-Specific Date)

Matt Travis
Matt Travis ✭✭
edited 03/31/21 in Formulas and Functions

I have set up a summary grid pulling information from another Support calls grid so I can keep track of logged calls completed by members of my team. I am currently using the formula below to pull that information for one of the team and it works fine.

=COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred")

The query I have is because I want to improve this. I would like to apply this to record only those calls completed in the current month and a second data set for only the current year. What would be the best way to accomplish this? I've only seen things for specific date ranges used so far which wouldn't be suitable for me.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    My apologies. I clicked on "Submit" before posting the year...

    =COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, IFERROR(YEAR(@cell), 0) = 2021)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    If you want current month and year, then you can use...


    =COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    For a single month (I will use March 2021 for this example) you wuld want:

    =COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2021))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    My apologies. I clicked on "Submit" before posting the year...

    =COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, IFERROR(YEAR(@cell), 0) = 2021)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Matt Travis
    Matt Travis ✭✭
    edited 04/02/21

    Thanks @Paul Newcome but if i'm reading this right, that references values as the current month or year. Is that right? So every time the month changed, I would have to go back in and change the number in the formula and same for the year.

    There must be a way to make this an automated process. I have created a column that will host a cell showing today's year and another showing today's month. I would like to reference those.

    I seem to have sorted this now via the formula below

    =COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Support calls Range 4}, IFERROR(MONTH(@cell), 0) = [This Month]1)

    The [This Month}1 refers to a column with my month date in as =MONTH(TODAY(0))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    If you want current month and year, then you can use...


    =COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2}, "Fred", {Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • So much easier. Thank you

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!