Count If / And

Kim Michael

I am trying to determine the sum of the "Deliverable Count". Sum will be counted if....

CountIF

"Project Phase" checkbox is checked or "Project Sub-Phase" checkbox is checked

AND

Status is: "Not Started", "In Progress", "Roadblock" or blank

Thank you for any guidance.

Paul Newcome

Try this...

=SUMIFS([Deliverable Count]:[Deliverable Count], [Project Phase]:[Project Phase], 1, Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Roadblock", @cell = "")) + SUMIFS([Deliverable Count]:[Deliverable Count], [Project Sub-Phase]:[Project Sub-Phase], 1, Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Roadblock", @cell = ""))

Kim Michael

You are soooo close. At least there is no error. But when I add the formula it does not appear to work for on of the situations. See below. It could absolutely be how I explained it in my original question.

With your formula, I would think the "deliverable count" should be 1 since there is "project phase" checked off and status is "roadblock"

Your thoughts or questions?

Paul Newcome

Ah. Ok. You said you wanted to SUM the Deliverable Count, so I thought you were putting numbers into the Deliverable Count column and then summing them based on your criteria.

Based on your most recent explanation and screenshot I see now you are wanting to COUNT the number of instances.

Try this instead...

=COUNTIFS([Project Phase]:[Project Phase], 1, Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Roadblock", @cell = "")) + COUNTIFS([Project Sub-Phase]:[Project Sub-Phase], 1, Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Roadblock", @cell = ""))

Kim Michael

YES! YES! YES. Thank you so much for your help. Sorry for any confusion I created with my explanation.

Paul Newcome

No worries. Happy to help. 👍️