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
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!