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:

Screen Shot 2022-07-29 at 12.24.37 PM.png

=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!

Find more posts tagged with

Accepted answers

All comments

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.