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
-
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
-
Thank you! What does the IFERROR do?
-
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
-
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))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!