Array formulas help you turn normal Excel Formulas into super formulas. Here are a few examples of Array formulas. These examples highlight the use of multiple conditions within a single array formula and how we can club multiple formulas in to a single array formula.
Array formulas are powerful – no doubt about that. However before we go any further, one must look at the flip side to using array formulas. They take much more computational power and can slow down the responsiveness of your worksheet if used indiscriminately.
Let’s take the example of 4 students who were all very keen to undergo tests to have their proficiency in Excel tested by an Expert. The Expert being a wise man, devised two series of Exams (I call them “Excel-Basic” and “Excel-Advanced”). Each series in turn consisted of 5 individual papers (Charts, Data Analysis, Loops, Pivot Tables and VBA). The tests were administered with each student undergoing 10 separate exams (5 in each series, 2 series in all). The results were tabulated as follows:
The Expert now showed the students how to use single array formulas to get information out of the table instead of having to write multiple formulas.
Array Formulas with the AND operator
Let’s say we wanted to know the sum marks for a student named “Able”. We would simply write =SUM(IF((C2:C41=”Able”),D2:D41,0)). The formula checks each cell in the array (C2:C41) to see if it contains “Able”. If it does, the IF function returns the corresponding score from column D, else 0. This way, we use the IF function to return an array of values. The SUM function then simply sums up the array and given the sum of marks scored by “Able”. (This is a result that could have easily been achieved using a simple SUMIF() function. An array formula has been used here just to built a base using which we will look at some more complex formulas later. Also bear in mind that the array formulas have to be entered with CTRL+SHIFT+ENTER and not a single ENTER (return key) as is the case with normal excel formulas.)
Now let’s say we also wanted to know the sum of marks scored by “Able” in the subject “VBA”. We could write =SUM(IF(((C2:C41=”Able”)*(B2:B41=”VBA”)),D2:D41,0)). Notice the use of the AND operator (*) between the two conditions. Again, the IF formula in this case generates an array of scores. Wenever both the conditions (Name = “Able” and Subject = “VBA”) are met, the corresponding value from Column D is returned, otherwise it will return a 0. The final output is the sum of all scores where the two conditions are TRUE.
Array formulas can also be used to check out for multiple conditions. Say we wanted to know the marks scored by “Able” in the subject “VBA” in “Excel-Basic” series. In this case, we will have to use three AND conditions 1) Name =”Able” AND 2) Where Subject “VBA” AND 3) Series = “Excel-Basic”. And therefore we write =SUM(IF(((C2:C41=”Able”)*(B2:B41=”VBA”)*(A2:A41=”Excel-Advanced”)),D2:D41,0)). if you noticed, we use the AND operator (*) multiple times to join various conditions.
Array formulas can also be used find out the maximum and minimum values for a given set of conditions. Suppose you wanted to find out the maximum value of marks scored by “Able” in any test. You can write =MAX(IF(((C2:C41=”Able”)),D2:D41,0)). The IF condition simply generates an array of scores where ever the condition is met. The MAX function then chooses the highest value out of that array of values.
Array Formulas with the OR operator
All the above examples that we’ve seen so far have used the AND (*) operator. However expressions can be combined using the OR operator (+) also. Let’s say you wanted to find out the average score by all students in subjects “VBA” and “Loops”. We could write something like =AVERAGE(IF(((B2:B41=”VBA”)+(B2:B41=”Loops”)),D2:D41,””)). Notice the use of the OR operator (+). The OR operator combines the two conditions in such a manner that even in one is TRUE, the scores are returned from Column D. However if both evaluate to FALSE, nothing is returned. The AVERAGE function simply averages the array returned by the IF function. (When both the conditions are FALSE, we have used the “” syntax to ensure that nothing is returned and that the AVERAGE functions averages over only the relevant values.
Let’s take an example of an array formula with both OR and AND conditons. Suppose we wanted to find the average marks scored by students in “VBA” + “Loops” in the series “Excel-Basic”. Here we use the OR operator in conjunction with the AND operator. We would thus write =AVERAGE(IF((((B2:B41=”VBA”)+(B2:B41=”Loops”))*(A2:A41=”Excel-Basic”)),D2:D41,””))
Notice the use of brackets with the conditions joined by the OR operator being enclosed together in a bracket and then used together with the AND condition.
Array Formula to return a COUNT based on single or multiple conditions
Array Formula to match a portion of a string
Here are a few examples of the array formulas based on matching a portion of a string. These can be used to find the number of times (first occurrence only) a particular string exists in a given range of cells.
The above are a few examples of Array formulas with moderate complexity. You can read more about the basic syntax of array formulas here.