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 😀
Best Answer
-
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 😀
Answers
-
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))))
-
#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?? 😊
-
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 😀
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives