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.

Find more posts tagged with

Accepted answers

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.