Countifs formula help
Hi There,
I've been looking all afternoon to see how I can complete my countifs formula and I think I'm close.
I need to count the number of rows between a date range based on if another column states a certain value. So in the case below, if the date is between X and Y in the 'date of incident' column and if the department involved is selected as fabrication.
So far i have
=COUNTIFS([Date of Incident]:[Date of Incident], AND((@cell >= DATE(2023, 8, 1), @cell <= DATE(2023, 8, 31))), AND([department
its the last part i cant get working, where it will count the rows if the department involved is filtered to fabrication.
Any help would be greatly appreciated.
Best Answer
-
Hi @James Brett
Yes, you can add ranges and criterion. For instance, regarding your first example, the formula might look something like:
=COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication", [Department Involved]:[Department Involved], AND("Fabrication", "Design"))
Note that I've not checked this formula. I've simply expanded the syntax provided in the COUNTIFS Function
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Answers
-
Hopefully the above makes sense
-
Hi @James Brett
Would the following work for you?
=COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication")
This is based on the syntax of the COUNTIFS Function
COUNTIFS( range1 criterion1 [ range2criterion2... ])
You'll note the reduction in brackets around the AND function, and there's no need to start the second (and subsequent) range/criterion with an AND.
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Hi Jason,
This is great thanks, seems to have worked. Just out of curiosity, is it possible to put another set of criteria in the equation, so for example, if the department involved had fabrication and design in the cell, as well just fabrication, or we also wanted to filter a project name in the 'project name and customer name' column?
kr
-
Hi @James Brett
Yes, you can add ranges and criterion. For instance, regarding your first example, the formula might look something like:
=COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication", [Department Involved]:[Department Involved], AND("Fabrication", "Design"))
Note that I've not checked this formula. I've simply expanded the syntax provided in the COUNTIFS Function
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Ideal thanks Jason much appreciated,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!