COUNT cells which CONTAIN two seperate column values
All,
I have a large database of information that I want to report from. For simplicity imagine 3 columns of data,
- first column (REF: Coloumn A) has unique ident for a project
- second column (REF: Column B) has status of "LIVE, ONGOING, COMPLETE" which are unique (i.e. cannot be LIVE and COMPLETE)
- third column (Column C) has Focus Areas that could have multiple inputs but are from a prescribed list say "COST IMPROVEMENT", "REDUCE RISK", "AID DEVELOPMENT".
I want to count the number of projects which are LIVE AND reference in Column C words "COST IMPROVEMENT". In many cases, as multiple selection possible - it may contain more than one selector. I have worked out how to do this if Column C only references COST IMPROVEMENT reference via
=COUNT(COLLECT({COLUMN A}, {COLUMN B}, "LIVE", {COLUMN C}, "COST IMPROVEMENT"))
but I cannot work out how to COUNT for projects both LIVE and where the cell contains the word COST IMPROVEMENT for multiple selection. Anyone help to amend the above syntax or tell me how?
Best Answers
-
Try this...
=COUNTIFS({Column B}, @cell = "LIVE", {Column C}, CONTAINS("COST IMPROVEMENT", @cell))
-
You legend. Thank you. That worked.
Answers
-
Try this...
=COUNTIFS({Column B}, @cell = "LIVE", {Column C}, CONTAINS("COST IMPROVEMENT", @cell))
-
You legend. Thank you. That worked.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 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!