Excel: Conditionally Select Range of Cells Based on Values in Other Column

Question

I can do things like this: =SUM($E2:$E1916) or =MEDIAN($E2:$E1916)

I'd like to perform the arithmetic on a subset of column E based on the values in another column. So, in column H the cells have a value of "Ford", "GMC", "Chrysler", "Toyota", "Honda" or "Subaru".

I want the sum or median or similar operation on column E to only select the cells in col E where the corresponding value in column H is "Toyota", "Honda" or "Subaru".

So the formula would look something like

=SUM(if($H2:$H1916={"Toyota","Honda","Subaru"},$E2:$E1916))

But that formula gives an answer of zero.

Answer 1

Your formula is correct. Just press Ctrl+Shift+Enter instead of just an Enter since it should be functioning as an array formula by construction.

But if you don't want to use an array formula you may construct a SUM formula as follows:

=SUMIFS($E2:$E1916, $H2:$H1916, "Toyota")+SUMIFS($E2:$E1916, $H2:$H1916, "Honda") + SUMIFS($E2:$E1916, $H2:$H1916, "Subaru")

• The first parameter is the values to be summed. You may use E:E instead.
• 2nd parameter is the column which contains all the values which will be compared to our criteria.
• 3rd parameter is our criteria.