COUNTIFS with Multiple Criteria
HI all, hoping that someone can help me out please.
I have two spreadsheets. One is a tracker, and one is to collect metrics.
On the Metrics sheet, I want to count all of the items in the tracker sheet in one column, that meet one of three criteria from a second column, and then also fit within a range in a third column.
Here are a couple of my attempts (this has eaten up my day today):
=COUNTIFS({Proposal Tracking CY2023 Range 4}, {Proposal Tracking CY2023 Range 3}, "4 - Delivered", OR {Proposal Tracking CY2023 Range 3}, "5 - Awarded", OR {Proposal Tracking CY2023 Range 3}, "6 - Not Awarded", {Proposal Tracking CY2023 Range 5} > 0, {Proposal Tracking CY2023 Range 5, <1000000})
=COUNTIFS({Proposal Tracking CY2023 Range 4}, 1, [{Proposal Tracking CY2023 Range 3}, OR(@cell =“4 – Delivered”, “5 – Delivered”, “6 – Delivered”)], [{Proposal Tracking CY2023 Range 5}, >0], [{Proposal Tracking CY2023 Range 5}, <1000000])
Here's a (very hastily and poorly) redacted screenshot of the Tracker spreadsheet. Any help greatly appreciated!
Answers
-
You can use AND and OR functions with the @cell reference to collect multiple metrics for your COUNTIFS function, so you are on the right track with your 2nd attempt. Below is an example where the Status:Status part of the countif returns the 6 blue values, and the Price:Price part returns the 3 values in yellow. The intersection is 2 values which the formula then returns.
Using your ranges, I think it'd be something like this:
=COUNTIFS({Proposal Tracking CY2023 Range 4}, true, {Proposal Tracking CY2023 Range 3}, OR(@cell = "4 - Delivered", @cell = "5 - Awarded", @cell = "6 - Not Awarded"), {Proposal Tracking CY2023 Range 5}, AND(@cell > 0, @cell <1000000}))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Thanks Jason! It still came back unparseable, but I feel like your answer is getting me closer...
-
Hey @sduffer
It looks like there's just an extra } at the end that doesn't need to be there. Try it again:
=COUNTIFS({Proposal Tracking CY2023 Range 4}, true, {Proposal Tracking CY2023 Range 3}, OR(@cell = "4 - Delivered", @cell = "5 - Awarded", @cell = "6 - Not Awarded"), {Proposal Tracking CY2023 Range 5}, AND(@cell > 0, @cell <1000000))
If this doesn't work, please post a screen capture of how you have the formula typed in the cell, but block out sensitive data.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!