» Summing Across Multiple Sheets
CATEGORY - Excel Summing
VERSION - All Microsoft Excel Versions
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.
Book Store:
Recommended Books:
- Excel 2002 For Dummies®
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
- The Analysis and Use of Financial Statements
- Marketing Plan: A Handbook with Marketing Plan
- The Sweet Potato Queens' Big-Ass Cookbook and Financial Planner
- Keys to Reading an Annual Report (Barron's Business Keys)
Sheets with Different Names
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?
Sumif
Sabu
Thank you for the above formula for using SUMIF from multiple sheets. It really works fine. Great..
Sumif Or Vlookup Across Multiple Sheets
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!!!
