Blog
- Calculate the Sum of Varying Ranges Based on a Condition and Across Multiple Sheets
- Calculate an Average from Averages that are Based on a Condition
Calculate the Sum of Varying Ranges Based on a Condition and Across Multiple Sheets
Sheet1
Sheet2
Sheet3
Summary
In this example, Sheet1, Sheet2, and Sheet3 contain the sales data for two salespeople, and we want to sum the sales for one using a single-cell, mega-formula.
For each sheet, Column A contains the name. However, the column containing the sales differs for each one. In Sheet1, Column D contains the sales. In Sheet2, Column B, and in Sheet3, Column C.
For the Summary sheet, A2:A4 contains the sheet names, C2 contains the name of interest, such as John, and D2 contains the column of interest, which in this case is Sales.
Note that the sheet names are listed in a vertical range of cells, not horizontal. Otherwise the formula will not return the desired result.
So now we can use the following formula to return the sum of sales for John...
=SUM(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A5"),C2,OFFSET(INDIRECT("'"&A2:A4&"'!B2:D5"),,
MMULT(IF(T(OFFSET(INDIRECT("'"&A2:A4&"'!B1:D1"),,
COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("B:B")),,1))=D2,
COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("B:B")),0),
TRANSPOSE(COLUMN(INDIRECT("B:D"))^0)),,1)))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 625.
Sample Workbook: Download
Calculate an Average from Averages that are Based on a Condition
In this example, we have a table where A2:A5 contains the student name, and Column B through Column F contain the corresponding test scores.
Let's say that we want to use a single-cell formula to calculate the overall student average from the average test score of each student. And, let's say that we want to exclude the lowest and highest scores from each student's average.
In other words, for each student, we take their total score, subtract their lowest and highest scores, and divide by three to get their average. Then we use these individual averages to calculate the overall student average.
For this calculation, we can use the following formula that needs to be confirmed with just ENTER...
=AVERAGE(MMULT(SUBTOTAL({4,5,9},OFFSET(B2:F5,ROW(B2:F5)-ROW(B2),0,1)),{-1;-1;1})/3)
Based on the sample data, the formula returns 76.92, rounded to two decimal places.
Sample Workbook: Download