Creating Conditional SUM, MAX, and MIN formulas using Arrays

Array formulas are a very powerful tool in MS Excel. An array formula is a formula that passes several data values instead of just one data value. An array formula could increase efficiency by performing multiple calculations on several data values and by eliminating the need for many traditional formulas to perform a given calculation.

​In this tutorial, I will demonstrate how Arrays could help perform conditional SUM, MAX and MIN functions in a more efficient manner. Consider the data set below: 

Say we want to calculate the SUM of sales by region. Using an array, we could perform this calculation with a single formula. Here is how the array formula is written for the SUM of sales in the East region.
{=SUM(IF(C$3:C$14=G2,D$3:D$14))}

How this formula works?
This formula consists of an IF statement embedded within the SUM formula.
IF(C$3:C$14=G2,D$3:D$14)
The IF statement above returns all values in the Sales column where the region is equal to “East”.  Note: put in the $ sign for reference locking the range for Region (C$3:C$14) and sales (D$3:D$14).
After typing in the formula:  =SUM(IF(C$3:C$14=G2,D$3:D$14))  

push “SHIFT+CTRL+ENTER” at the same the time on the keyboard. Doing so will create the double bracket (i.e.{ })  surrounding the formula (Don’t try to insert he brackets manually, it won’t work!)

Now an array is created that stores the IF statement in the memory instead of having it stored in a separate cell. As such, the array allows two calculations (i.e. IF and SUM calculations) to be performed simultaneously and within the same cell, as oppose to inputting them in different cells.

As per the screenshot above, the array formula has returned the correct value for the sum of sales in the East region. Next step in to apply the formula to the other two regions (Make sure you have the reference lock for Region and Sales) by dragging it down.

​The same steps will return the MAX and MIN values for the each region. Just repeat the same procedure as above but enter MAX and MIN instead of SUM in the formula. Here is how it looks like:

---