Formula =COUNTIFS ... to not include certain text

Hey guys

So i have this formula

=COUNTIFS({Depots1}, [North(1)]23, {Product1}, FIND("Other", @cell)

it counts the amount if = depot is North(whatever is inputted at North 23 column/ row) and Product1 Contains the word "Other" ...

What I want to do is Count the amount of cells that depot is North, that the product column is not a blank cell and the product column does not contain Primary Column row 25 - row 47 / any of the following:

Primary Column row 25 - row 47

'Access, Compressors, Concreting, Consumables, Fence & Deck, Generators, Heat & Cool, Hoisting, Lifting, Lighting, MEWPS, Pipe Fuse Weld, Plant Mech, Plant non Mech, Powered Access, Pumps, Rail, Safety, Service, Survey, Tools, Traffic, Waste, Other'

I like to keep it simple and all lol 😀

Find more posts tagged with

Accepted answers

Stacy Meadows

Problem Solved with assistance,@Genevieve P.

here is how

=COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Product Name")))

The bold italic writing replaces column and row as it does not work this way ... So you need to "product name" and then it works

=COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Hammer")), {Product1}, NOT(HAS(@cell,"Spanner")), {Product1}, NOT(HAS(@cell,"Axe")))

and so on .... Works a treat ...

Thank you 😀

All comments

Sameer Karkhanis

Try this: (You can replace "North" with any specific cell if you want like in your formula.

=COUNTIFS({Depots1}, "North", {Product1}, AND(@Cell <> "", NOT(HAS([Primary Column]23:[Primary Column]47,@cell))))

Stacy Meadows

#unparseable is what that gives me....

=COUNTIFS({Depots1}, [Peterlee(1)]23, {Product1}, AND(@Cell <> "", NOT(HAS([Primary Column]24:[Primary Column]46, @cell))))

Any idea's?? 😊

Stacy Meadows

Problem Solved with assistance,@Genevieve P.

here is how

=COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Product Name")))

The bold italic writing replaces column and row as it does not work this way ... So you need to "product name" and then it works

=COUNTIFS({Depots1}, [Column1]23, {Product1}, <>"", {Product1}, NOT(HAS(@cell,"Hammer")), {Product1}, NOT(HAS(@cell,"Spanner")), {Product1}, NOT(HAS(@cell,"Axe")))

and so on .... Works a treat ...

Thank you 😀

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.