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.