How do you use an array formula to sum up element-wise minimum values?

Suppose you plan to buy several computer parts from either BestBuy or Circuit City. The two stores have different prices for each product. If you buy from one store exclusively, you won’t get the best deals possible. To optimize the value of your hard-earned dollar, you want to find the least amount of money necessary to buy one of each part.

99.5% of the time, you’d set up a spreadsheet similar to the one shown below. By using a MIN() function, you create a third column listing the lower price of each item. Then it’s a no-brainer to add up the column for the total.

Occassionally, you may want/need to calculate this number in one single step. Array formulas came to mind and it seems intuitive to write {=SUM(MIN([array 1], [array 2]))}. However, that doesn’t work because MIN([array 1], [array 2]) does not give you an array of element-wise minimum values… instead, it gives you a single minimum value between the two arrays.

To fix this problem, you can employ the IF() function within your array formula. This is short, clean, and easy to understand:

{=SUM(IF([array 1]>[array 2], [array 2], [array 1]))}

Or if you prefer to use the mask/filter approach, your formula could look like this:

{=SUM([array 1]*([array 1]<[array 2]) + [array 2]*([array 1]>=[array 2]))}

The ([array 1]<[array 2]) part creates a mask of 0’s and 1’s, which is used in a similar manner to the typical SUMIF-type of array formulas. The two approaches use the same logic, and the only difference is the appearance.

A problem is always simple when you already have the solution, but believe it or not, I came up with the most convoluted approach and a super long formula when this problem was first presented to me. I’ve included that formula in the sample spreadsheet – see if you can figure out what I was trying to do!

## Recent Comments