» Summing Across Multiple Sheets

Problem:

Column A of Sheet1 contains the letters A to D. Column B contains a set of corresponding numbers.
Sheet2 and Sheet3 have a similar layout.
Column A of Total Sheet contains the letters A to D as well.
We want to lookup each of the letters listed in column A of Sheets 1 to 3 and sum the corresponding numbers from column B of all three sheets.

Solution:

Use the SUMPRODUCT, SUMIF, INDIRECT, and ROW functions as shown in the following formula:
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!$A$1:$A$4"),A1,INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!$B$1:$B$4")))
Enter the above formula cell B2 of your Total Sheet, and copy it down the column.