Formula =COUNTIFS ... to not include certain text

Options

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

  • Stacy Meadows
    Stacy Meadows ✭✭✭
    Answer ✓
    Options

    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

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Options

    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
    Options

    #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
    Stacy Meadows ✭✭✭
    Answer ✓
    Options

    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 😀