Median formula with multiple criteria (or not and) and partial text in a cell

Hi. I'm trying to come up with a formula that allows me to pull the median value from a large data set that meets multiple criteria.

I've attached a sample of the data.

For example, pull all the occupancy numbers from properties containing "*Publix" in Anchors column and with a Building Status of "In Redevelopment," "New Delivery," OR "blank" (not "Under Construction") and give me the median of those. I would use the same formula to look up the median of "*Winn-Dixie."

Thanks for the help.

Glenn,

Thank you so much!! It works and I really appreciate it.

As a follow up question, imagine I had a lot of records with different grocery stores and non-grocery stores. If I wanted to find the median of just the grocery stores (Publix, Winn-Dixie, Whole Foods, Trader Joe's, etc.), how could I do that? I tried using the same formula as above and added all the ISNUMBER(SEARCH(G5,$C$2:$C$17)) for the different grocers, but than the formula became too long and it wouldn't work. I'm guessing there might be a way to do it using VLOOKUP. I would still need to not include the Under Construction properties. Then, if I wanted to not include the different grocers, and only pull the median of the non-grocery stores, I would guess I'd just use more of this IF($F$2:$F$17<>"Under Construction", but have it say the different grocers instead of Under Construction. There are about 25 different grocers, so I'm trying to use a formula that will work.

Thanks for all your help.

Glenn!!! You are amazing! I have no idea how that formula works, but it does...perfectly! Thank you!

I think this might be the last related question for a little while...how do I pull the median of all the properties that don't include those grocers in the name range (Stores)? In other words, show me the median of everything but those grocers.

Thanks so much!

Again, thank you, Glenn! You are an Excel mastermind! It worked beautifully and makes my life so much easier.

Hi, Glenn. I hope you're doing well.

I'd like to know how I would add an additional criteria to the formula you provided to me last year. In other words, I would like to see the median of all the properties that are not on that list AND that are NOT Under Construction AND are NOT Proposed (these last two are Building Status).

Thanks so much for your help!

Thanks, Glenn! And what would the formula be if I also wanted to exclude the Stores list (h14 formula) and not include Under Construction or Proposed?

I think it would be: MEDIAN(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(Stores),Raw!$C$2:$C$17)),0,1),(ROW(Stores)>0)+0)>1,IF($E$2:$E$17<>"Under Construction",IF($E$2:$E$17<>"Proposed",Raw!$D$2:$D$17,"")))).

That works for me, but then when I put it into my bigger spreadsheet and use the same idea, it doesn't calculate correctly.

This is the formula I'm using, which I tried to do exactly like your example one, but it doesn't work.

MEDIAN(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(Grocers),Data!$F$2:$F$18)),0,1),(ROW(Grocers)>0)+0)>1,IF(Data!$B$2:$B$18<>"Under Construction",IF(Data!$B$2:$B$18<>"Proposed",Data!$K$2:$K$18,""))))

Any thoughts?

Thanks so much!!

Hi, Glenn. Thanks. It looks like the same formula that I'm using, but there's something I'm obviously missing. I've attached the spreadsheet that I was referring to. The main problem is the median formula for non-grocery anchored centers on the 2nd tab.

Thanks for your help with this! You are a lifesaver!

Hi. Thanks for looking into this. I think the problem may be with the portion of the formula that I need in each of these median formulae (IF(Data!$I$3:$I$1321<>""). Since I have different property sets that I put into this spreadsheet--all with different numbers of rows--I need the formulae to be able to account for that and not "count" any of the blank rows in the Data tab. This is how the original spreadsheet is set up that I sent you (reattached).

Having this in my Totals - Grocery and my Totals - Grocery; Non-Publix works, but for some reason it doesn't when it comes to Totals - Non-Grocery. For some reason, adding that in there looks like it returns the median of all the properties, which is not what I'm looking for.

I do need the formula to have in there those blanks. Any ideas on how to structure it?

Thanks so much for your help!!

Thanks, Glenn!!! This works. I really appreciate you helping me out with this formula! Have a great day.