Summing Across Multiple Sheets

[Liam]

I have tried to adapt this formula to a spreadsheet where the sheets have different names, but have been unsuccesful. How would I go about doing that?

[Sabu]

Thank you for the above formula for using SUMIF from multiple sheets. It really works fine. Great..

[ExcelTip]

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.

[ALAVERY]

Good day.

If anyone can help that would be GREAT since I'm very stumped!

I am trying to VLOOKUP or (lookup via SUMIF)

I have multiple worksheets. For simplicity the first two worksheets are named "NCF" and "PCF"

I have assigned an individual number to each entry in (NCF,PCF) which is (AZ) and is the criteria for the SUMIF (or column 19 for the VLOOKUP range (AZ3:CH121)

These are some formulae I have tried which will not work:

SUMIF(NCF:PCF!AZ3:AZ200,A3,NCF:PCF!BV$3:BV$200)

SUMIF(NCF!$AZ$3:$AZ$200,$A3,NCF!BV$3:BV$200)

SUMIF(INDIRECT({"PCF","NCF"}&"!$AZ$3:AZ$200"),$A4,INDIRECT({"PCF","NCF"}&"!$AR$3:AR$200"))

Any ideas would be appreciated.

Thanks!!!