Countifs with multiple criteria along with a date range.

Hi,

I am trying to pull specific data from my sheet along with a date range and the formula works fine up until I put in the date. This is what I am currently using:

=COUNTIFS([NOW Case Details]:[NOW Case Details], "Meeting SAP Ariba Standard", Portfolio:Portfolio, "Business Network", Pod:Pod, "Seller Apps", AND([Today's Date]:[Today's Date], >=DATE(2022, 7, 1), [Today's Date]:[Today's Date], <=DATE(2022, 7, 31)))

Thanks!

Answers

  • Dan W
    Dan W ✭✭✭✭✭
    edited 07/29/22

    Give this a try

    =COUNTIFS([NOW Case Details]:[NOW Case Details], "Meeting SAP Ariba Standard", Portfolio:Portfolio, "Business Network", Pod:Pod, "Seller Apps", [Today's Date]:[Today's Date], IFERROR(MONTH(@cell), 0) = 7)


    This will check all of month 7

  • eli7389
    eli7389 ✭✭

    Thank you! What does the IFERROR do?

  • Dan W
    Dan W ✭✭✭✭✭

    @eli7389

    The IFERROR allows us to replace the output of the formula if there is an error. Sometimes with dates if the date is empty it will throw an error. Here is the Smartsheet link that explains it better than I can

    https://help.smartsheet.com/function/iferror

  • DR Brian
    DR Brian ✭✭✭

    FYI -- Can't do an AND statement within the COUNTIFS -- should be separate range/criterion or simplified into single like Dan's suggested.

    Reworking your original formula keep in mind all COUNTIFS criteria are inherently AND

    =COUNTIFS([NOW Case Details]:[NOW Case Details], "Meeting SAP Ariba Standard", Portfolio:Portfolio, "Business Network", Pod:Pod, "Seller Apps", [Today's Date]:[Today's Date], >=DATE(2022, 7, 1), [Today's Date]:[Today's Date], <=DATE(2022, 7, 31))

  • eli7389
    eli7389 ✭✭

    Thanks everyone for all of those insights, this was super helpful and the formula now works. One additional question - would there be a way to automate the formula so it only counts for the current month? I am using this formula for a dashboard and would like for it to be as automated as possible.

    Thanks

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!