i think countif formula but not sure..

[ooom]

I have this sheet here with 40 locations which are the same but are over 3 different warehouses, where there is a location full in one warehouse there is one empty in another, and also there is locations occupied in all 3 warehouses..i am trying to write a formula which out of the 40 total locations over the 3 warehouses will give me the number of locations empty without counting duplicates as all 3 warehouses and locations exist under the one building..there is "yes, no" against locations throughout the sheet against the different warehouses indicating if there occupied or not..i have tried and researched this but have had no success so far...

Credit to adhawan06 who helped me with first part of this spreadsheet

[Glenn Kennedy]

Hi htere. I didn't really understand exactly what you wanted. So here's a first go. I'll explain the details once we get an end result. Basically, I made a unique list of all the locations and counted the SKUs at each location (irrespective of the warehouse). If the value was >0, I returned "No Stock". You could also have had the actual numbers for each location.

[ooom]

it is good Glenn, thank you...first go is what i needed..
i see you used a array formula , thank you for your time and willingness to give it a go, i am not the best at explaining correctly where my troubles are.
you mentioned ,
"You could also have had the actual numbers for each location", what could of i done to make it easier achievable in what i wanted ??
explaining details will be great,

[Glenn Kennedy]

The unique list of locations was made using a Named Range (called List). Hit CTRl and F3 to see how it was set up. nYes, I then used an array formula (sorry - should have mentioned that) to return the list of unique locations. The named range makes it easier to cope with additions to the list of locations. It'll automatically update as far down as the formula goes (the end of the green cells). Drag down further if needed. The SUMIF totalled the SKUs at each location. In my previous version, it was set to return "No stock" if the total was zero. here, it'll return the total number of SKUs (but it won't tell you WHERE they are).

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

Any other problems - shout.

[ooom]

thank you Glenn, the second version is great, you done the rest i wanted to try to do myself , thank you...
both sheets are great and i appreciate your help...
i do see named ranges, and am dissecting the formulas so i know for next time...

Any other problems i will defiantly shout out,

Thank you

[adhawan06]

Hi Ooom - What my understanding says that you are looking to have total empty location for the for each location across 3 warehouse.

You can continue using Glenn file which Glenn Shared and use the following formula in column J2 and drag it down.

Please Login or Register  to view this content.

EDIT : This will return to 2 (For e.g. K6011B is full in "AJA" however available in "BJB" & "CJC")

I hope this is what you are looking forward.

Please let me know if it solve your purpose.

Cheers!!!

Anil Dhawan

[ooom]

Thank you adhawan06 for your added formula, it is most beneficial and is giving me a lot to work with....great...
the solutions from yourself and Glenn are far beyond what i expected to achieve 2 hours ago....

it does solve my purpose in a big way and is greatly appreciated..

[adhawan06]

Thanks ooom for adding feedback and reputation. Glad to help you...

Keep visiting here and posting your queries.



Cheers!!
Anil Dhawan