COUNT cells which CONTAIN two seperate column values

Luke Marshall

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?

Paul Newcome

Try this...

=COUNTIFS({Column B}, @cell = "LIVE", {Column C}, CONTAINS("COST IMPROVEMENT", @cell))

Luke Marshall

You legend. Thank you. That worked.

Paul Newcome

Try this...

=COUNTIFS({Column B}, @cell = "LIVE", {Column C}, CONTAINS("COST IMPROVEMENT", @cell))

Luke Marshall

You legend. Thank you. That worked.

Paul Newcome

Happy to help. 👍️