COUNTIFS and NOT Formula Creation

Hello,

I have a table of data that looks like this:

I am needing to create a formula that will count all of the "1" in each column, that do not contain "OPEN" in the name field. So far I have created this formula:

=COUNTIFS(Sun3:Sun27, 1, Employees3:Employees27, NOT("OPEN"))

Does anyone have an idea for a formula that will count all of the 1's connected to employees with names?

Thanks!

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

All comments

Paul Newcome

Are you getting an error message or an incorrect count?

Ruby Wood

@Paul Newcome hello! I am getting "#Invalid Operations" when I enter this formula.

Mary Royston

Hey @Ruby Wood

You can try: =COUNTIFS(Sun3:Sun27, "1", Employees3:Employees27, NOT(CONTAINS("open", @cell)))

Hope that helps :)

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.