Using a COUNTIF but need two criteria
Hello,
I have a master data sheet with all my information and another sheet where I have my KPIs/metrics & formulas. I am currently using a COUNTIF formula
=COUNTIF({Project List Range 1}, Labels@row)
this is counting how many projects I have in each status (new, in process, pending, etc.) I would like to add another criteria to this to only count the line if the 'Project Type' column says external. Am i able to do so and how would I add to my existing formula?
thanks!
Best Answers
-
You just need to change the COUNTIF to COUNTIFS and add your 2nd range and 2nd criteria
=COUNTIFS({Project List Range 1}, Labels@row, Range2, Criteria2
-
I recommend using the COUNTIFS formula for multiple criteria. Example below!
=COUNTIFS({Project List Range 2}, "External", {Project List Range 1}, Labels@row)
Project list range 2: the project type
Project list range 1: the project status
Answers
-
You just need to change the COUNTIF to COUNTIFS and add your 2nd range and 2nd criteria
=COUNTIFS({Project List Range 1}, Labels@row, Range2, Criteria2
-
I recommend using the COUNTIFS formula for multiple criteria. Example below!
=COUNTIFS({Project List Range 2}, "External", {Project List Range 1}, Labels@row)
Project list range 2: the project type
Project list range 1: the project status
-
Hello- I am trying to follow the syntax recommended here but I am still getting an invalid reference.
I am trying to create a summary formula for all active risks in my QRAIDD log. What am I missing here?Type = Risk and Status=Open
=COUNTIFS({Type:Type}, "Risk", {Status:Status}, "Open")
Thanks for your help
-
If your formula above isn't a cross sheet references, then you need to remove the brackets. If it is Cross Sheet, then also needs edit. Something along the lines of:
In same Sheet
=COUNTIFS(Type:Type, "Risk", Status:Status, "Open")
Cross Sheet
=COUNTIFS({Type}, "Risk", {Status}, "Open")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!