Countifs with multiple criteria along with a date range.

eli7389

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!

Dan W

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

Thank you! What does the IFERROR do?

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

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

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