COUNTIFS with NOT CONTAINS from a Referenced sheet with two columns selected
Hello! I've searched through a few community posts but cannot seem to find an answer to my formula
I'm trying to calculate how many products we have in one column that are not "done" from another column that are both being referenced from the same sheet
where:
{product} = Product1, Product2
{status} = Done, Ready for Acceptance, In Progress, To Do, Requested
right now I have my formula as:
=COUNTIFS({product}, "Product1", {status}, NOT(CONTAINS("Done", {status})))
I've tried multiple variations of this and can't seem to figure it out. I've also tried just contains with adding all the other selections except Done and that hasn't worked either. I know I read somewhere about the limitations of using CONTAINS in COUNTIFS, so happy to try another formula if someone can figure it out!
Many thanks
Best Answer
-
Maybe you're overcomplicating things - try this (assuming your status column can only contain one value):
=COUNTIFS({product}, "Product1", {status}, <> "Done")
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Answers
-
Maybe you're overcomplicating things - try this (assuming your status column can only contain one value):
=COUNTIFS({product}, "Product1", {status}, <> "Done")
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Hi Ramzi,
Thanks so much for your response- this worked!
I do have another instance in which I want to show which countries {market} are not "done", but a user can select multiple countries per cell, where the selections are US, EU, JP, CN.
If I want to do the same thing, where I show 4 columns to represent the 4 regions for rows that are not "done", is it the same formula or different now that I have to account for multiple options?
Thanks!
-
So one column with selected regions (multiselect) and then one column for each separate region's status?
Maybe a screenshot will help.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Hi Ramzi,
See below- with the formula you provided above I think its not including rows that have multiple markets selected. Each Row is a different product (Product 1, Product 2, etc.) but in this case I just want to know the total products for each region that are not "Done". let me know if that helps
-
You would want to use the CONTAINS function. So maybe something like this:
=COUNTIFS({product}, "Product1", {status}, <> "Done", Market:Market, CONTAINS("US", @cell))
this would be for the US for example.
Does that help?
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!