Performing a SUMIF Across Multiple Sheets with Varying Sum Ranges
In some rare circumstance, one might want to perform a SUMIF over several sheets, where the location of the sum range differs for each one. So, let's assume that we have the following data...
Sheet1
Sheet2
Sheet3
For this example, let's say that we want the total sales for John from Sheet1, Sheet2, and Sheet3. On a separate sheet, list the sheet names in a vetical range of cells, let's say A2:A4. Then, let C2 contain "John", and D2 contain "Sales". Now, we can use the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...
=SUM(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A5"),C2,OFFSET(INDIRECT("'"&A2:A4&"'!B2:E5"),,
MMULT(IF(T(OFFSET(INDIRECT("'"&A2:A4&"'!B1:E1"),,COLUMN(INDIRECT("B:E"))-
COLUMN(INDIRECT("B:B")),,1))=D2,COLUMN(INDIRECT("B:E"))-COLUMN(INDIRECT("B:B")),0),
TRANSPOSE(COLUMN(INDIRECT("B:E"))^0)),,1)))
Based on the sample data, the formula returns 625...
Sample Workbook: Download
Averaging the Average Test Score of Each Student
In this example, we have five students, and each student has taken five math tests. As you can see from the following table, Column A contains the student name, and Columns B, C, D, E, and F contain the corresponding test scores.
Let's say that you'd like to calculate an average from the average test score of each student, but that you'd like 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, you can use the following formula that needs to be confirmed with just ENTER...
=AVERAGE(MMULT(SUBTOTAL({4,5,9},OFFSET(B2:F6,ROW(B2:F6)-ROW(B2),0,1)),{-1;-1;1})/3)
Based on the sample data, the formula returns 76.53, rounded to two decimal places.
Sample Workbook: Download
Last Update: May 3, 2012