Count if formula with multiple criteria where a field contains certain text

Hello,

I am having trouble writing a formula to count rows. Where row 1 = Yes where row2 does not contain "virtual" or "needed" (these single words are fragments of the selection in the drop-down list). This formula is on one sheet and counting values from a different sheet.

I can get this to work in reverse ie count row 1 if yes and count row 2 where row2 contains "Virtual" or "Needed" but I can't get it to work with the Not.

I have tried it both of the ways below.

=COUNTIFS({Summer Program}, "Yes", {On site desk 1}, OR(CONTAINS(NOT("Virtual", @cell), CONTAINS(NOT("needed", @cell)))))

=COUNTIFS({Summer Program}, "Yes", {On site desk 1}, OR(NOT(CONTAINS("Virtual", @cell), CONTAINS("needed", @cell))))

Find more posts tagged with

Accepted answers

All comments

Lucas Rayala

Hi @latheobald89201, I'm pretty sure if you try to put two "Not" statements together in an "Or", you will select everything (if you are pointing this at a single-select pulldown).

Try breaking them up:

=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)))

latheobald89201

@Lucas Rayala Thank you that is close it is also counting blanks. I tried adding NOT(ISBLANK but I just can't get the syntax right

=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)),{On site desk 1}, NOT(ISBLANK, (@cell)))

Lucas Rayala

Hi @latheobald89201, you need to exclude the extra coma in front of the "ISBLANK" term in your formula.

=COUNTIFS({Summer Program}, "Yes",{On site desk 1}, NOT(CONTAINS("Virtual", @cell)), {On site desk 1}, NOT(CONTAINS("needed", @cell)),{On site desk 1}, NOT(ISBLANK(@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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.