How to use a countif formula excluding specific criteria?

Ideally I am looking to write a countifs formula to represent the reporting teams count of active task by complexity, priority, and status with the exception that the completed and back log items should not be included in the active tasks total as they are not truly active

I was able to write a formula however my formula includes completed and backlog therefor the data I am reporting out is not true/correct

Looking to show that the reporting group has 1 active low complexity task due to the fact out of all of the low complexity tasks only one is truly active

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

This is the basic idea.

=COUNTIFS([Group]:[Group], "Reporting", [Complexity]:[Complexity] = "Low Complexity", [Priority]:[Priority] <> "Backlog", [Status]:[Status] <> "Backlog", [Done]:[Done] = 0)

Whether you are putting this somewhere in the same sheet, a different sheet, or in the Sheet Summary will determine the exact syntax.

Paul Newcome

means "not equal to".

This = That --> This equals That

This <> That --> This is not equal to That

Hollie Green

=Countifs([Priority]:[Priority],"Priority 1",[Status]:[Status],<>"Completed",[Complexity]:[Complexity],"Low Complexity")

The above formula will count all Priority 1 with a low complexity that is not completed. It will exclude the completed with the <>"Completed" part of the formula and will exclude the backlog because it is only looking for "Priority 1" and all of you backlog are marked in that column.

You can adjust the formula to get your other counts of Priority and Complexity by changing the criteria "Priority 1" and "Low Complexity"

Andoni

Hello and happy new year! @Paul Newcome First post in the community so apologies if this is misplaced, but I am trying to do exactly the same thing as the original comment but as part of a larger COUNTIFS formula, attached below for reference -

=COUNTIFS(Initiative:Initiative, CONTAINS("AEM", @cell), [Target End Date]:[Target End Date], IFERROR(MONTH(@cell), 0) = 1, Status:Status <> "Complete")

For additional context, Initiative, Status and Target End Dates are all columns in my sheet. The formula works perfectly without the last criterion (i.e. Status:Status <> "Complete") but I am not sure what the problem is. The error returned is #Invalid Operation. Status is a dropdown-only column, restricted to a list of values that includes Complete.

What I am trying to achieve above is to count the values that fit a certain project group (hence the first criterion, so I can continue adding rows without having to change the formula), by due month (hence the second criterion). I want to filter out completed tasks, as these are not relevant for this calculation.

Thank you in advance!

Paul Newcome

@Andoni You need a comma between the range and criteria to follow proper syntax.

Andoni

Can't believe I missed that - thank you so much @Paul Newcome !

22Wordsmith

@Paul Newcome - I have a text string to exclude. My formula

=COUNTIFS({ReportM}, "Unassigned", {ThemesM}, Metric@row)

counts everything. I need to exclude items that appear in a cross-sheet reference that I'll call {RefM} which start with "Pre-Poll"

I tried

=COUNTIFS({ReportM}, "Unassigned", {ThemesM}, Metric@row, {RefM}, "<>Pre-Poll*")

but that didn't work. The asterisk was because the format is Pre-Poll and up to four digits.

Paul Newcome

@22Wordsmith You would need to use a NOT/CONTAINS combo instead.

NOT(CONTAINS("Pre-Poll", @cell))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.