Excel - minifs function returning 0

Question

I'm trying to write a function in Excel 2013 that returns the minimum of a set of cells that meets two criteria, but the function always returns zero.

On one worksheet, I have a table with several columns containing information about different apartment floorplans, locations, and monthly rents. Each row represents one floorplan. The important columns are:

• R: contains a code indicating how many bedrooms the floorplans has
• S: contains a code indicating what city the floorplan is in
• L: contains rent for the floorplan.

On the second worksheet, I have a table with several columns. Each row shows characteristics about a given floorplan type in a given city. The first two columns contain codes to tell Excel which bedroom type/city pair I want the minimum of. Column A contains city codes and Column B contains bedroom type codes. I want Column J to show the minimum rent for the bedroom type/city pair in the same row.

I have been trying to do this by writing a min function with two embedded if's, but the function keeps returning zero. Anyone know why that might be? Here is the formula I have been trying:

=MIN(IF(Worksheet1!$R$13:$R$148=Worksheet2!$B11,IF(Worksheet1!$S$13:$S$148=Worksheet2!$A11,""),""))

Solution

Basically, if we break your formula down, it's similar to this -

=MIN(IF(A1:A10=C1,IF(B1:B10=D1,""),""))

So if anything in A1:A10 is equal to C1, then check if anything in B1:B10 is equal to D1 and return... nothing "" and then take the minimum value of.. nothing "".

That is why it's failing. It sounds like what you want to do is match the pair in C1 and D1 and return a different value in column L, but this isn't anywhere in the formula.

---

What I think you're trying to do is take a pair and match them to another column, then taking the minimum value of another column that's offset to every match that occurs.

This formula will take the minimum the way you want, based on one criteria (CtrlShftEntr)

=IF(COUNTIF(A1:A10,D1),MIN(IF(A1:A10=D1,C1:C10)),"")

What I'm not sure about is if you need to match both criteria in the same row, or if you can match them in different rows.

Comments

[Beth]

What do you mean by this?: "What I'm not sure about is if you need to match both criteria in the same row, or if you can match them in different rows." Each row represents a single floorplan. I have several of each pair of criteria, and each pair is associated with a value. I am trying to find the minimum value associated with each pair/floorplan "type."

[Beth]

But ah! Per your first comment, I added an instruction to return a different value in column L, and the formula works now, yes! The second formula you suggested works for the first pair, but it returns the same value for each pair (which is not correct). Do you know why that would be?