Help on NOT function within COUNTIF(S)!

Hi all,

I am trying to create a formula for a sheet summary. I have a "Chem" column that is populated from a form, and could be any one of 26 chem types. I want my summary to return the total number of chems within the Chem column, but I don't want it to include a specific chem, WOS, AND I don't want it to count blank cells.

I tried COUNTIFS function with NOT:
=COUNTIF(Chem:Chem, <>"")NOT(@cell = "WOS")

I almost tried a COUNTIFS and set my criteria as all those chemistries, but I would like to avoid having to type out all 26 kinds in a formula.

But I keep getting either an incorrect argument set or unparsable error every combination I try! And help or advice is well appreciated!!

Find more posts tagged with

Formulas

Accepted answers

bisaacs

Hey @nroth,

It looks like you have the brackets in the ranges entered incorrectly. When a column only has one word (like "Chem"), no brackets are required:

Chem:Chem

If the column name has spaces/special characters, then you use two sets of brackets:

[Chem test]:[Chem test]

If you fix that then it should work!

All comments

bisaacs

Hey @nroth,

It looks like how you're formatting the formula is incorrect. If you want to use a NOT with a COUNTIFS, then you'd do something like this:

=COUNTIFS(Chem:Chem, <>"", Range:Range, <>"WOS")

Hope this helps!

nroth

Hi @bisaacs !

Thanks for responding to quickly!! I tried that formula you gave me, and it is still giving me an unparsable error. This is the exact formula I put in:

=COUNTIFS([Chem:Chem], <>"", [Chem]:[Chem], <>"WOS")

Is it an issue having the same range in the formula for two different expressions? Not sure, but I added a screen shot of the sheet in case that helps!:

Highlighted is the Chem column in question, they populate from another sheet when conditions are met, hence why I don't want the formula to count blank cells! In this specific scenario, I want it to return "0" under Approved, since it is only the WOS on the sheet. Let me know your thoughts!!

bisaacs

Hey @nroth,

It looks like you have the brackets in the ranges entered incorrectly. When a column only has one word (like "Chem"), no brackets are required:

Chem:Chem

If the column name has spaces/special characters, then you use two sets of brackets:

[Chem test]:[Chem test]

If you fix that then it should work!

nroth

@bisaacs

Oh wow, now I have lots of formulas to update and remove brackets from!! That worked though, thank you so much for all your 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.