Countif multiple range
I want to calculate Ticket Priority with a specific date; how to use the counif formula like
How many tickets which Have Priority Normal and received in the month of June Only
Best Answer
-
Like this:
=COUNTIF(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)))
If you have additional criteria (such as in the first example, you would switch to COUNTIFS:
=COUNTIFS(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)), Type:Type, "Normal")
If you need to change the range then just alter the numbers in the DATEs (Year, Month, Day).
Hope this helps! 🙂
Answers
-
=COUNTIFS([Date]:[Date], MONTH(@cell) = 6, [Priority Level]:[Priority Level], "Normal")
-
this gives me the error message invalid Data type
-
Is your Date column set to Date as type? If not, the MONTH portion of the formula won't work properly.
-
Itis set to Date as type
-
In which case, amend the formula above slightly:
=COUNTIFS(Date:Date, IFERROR(MONTH(@cell), 0) = 6, [Priority Level]:[Priority Level], "Normal")
This should give the desired result.
-
This is working thanks alot
-
Can you help me same with the average Formula? with month
-
It should be along the lines of:
=AVERAGEIF(Date:Date, IFERROR(MONTH(@cell), 0) = 6, [Resolution time (min)]:[Resolution time (min)])
Hope this helps!
-
Thanks@Nick Korna
-
How to use this formula for specific date Range like 6/10/2023 till 6/24/2023
-
Like this:
=COUNTIF(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)))
If you have additional criteria (such as in the first example, you would switch to COUNTIFS:
=COUNTIFS(Date:Date, AND(@cell >= DATE(2023, 6, 10), @cell <= DATE(2023, 6, 24)), Type:Type, "Normal")
If you need to change the range then just alter the numbers in the DATEs (Year, Month, Day).
Hope this helps! 🙂
-
Thanks@Nick Korna
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!