Sumif over multiple sheets

Question

Hi Team,

Let's say I have a workbook with multiple worksheets. And I have a main file that calculates the sum based on row name across multiple worksheets. here, I am only providing 1 condition saying the name should match, but in my original dataset, I have 3-4 categories I need to match to sum up across multiple worksheets.

Sharing an example and am looking for a formula that I could use to sum up.

For eg: On sheet 1 have this: I need marks ás total for the specific name

Name	Marks
Srujan	70 (Based on sheet 1 + sheet 2)
Ram	80 (Based on sheet 1 + sheet 2)
Shyam	127 (Based on sheet 1 + sheet 2)

Now in sheet 2, I have:

Name	Subject	Marks
Srujan	Science	50
Ram	Science	40
Shyam	Science	67

And in sheet 3, I have

Name	Subject	Marks
Srujan	Maths	20
Ram	Maths	40
Shyam	Maths	60

Answer 1

Share more conditions then we will give you formula suggestions.

=SUMIF(Sheet2!A:A,Sheet1!A2,Sheet2!C:C)+SUMIF(Sheet3!A:A,Sheet1!A2,Sheet3!C:C)

You may use power query to combine your tables, after that your formula will be easier.

You can also get what you want directly in Power query.

Answer 2

On the summary sheet, create a list of the sheet names that you want to include in the sum.

Select the list and name it (for example) Sheets.

Let's say the student names are in A2 and down on the summary sheet.

In B2, enter the formula

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"), INDIRECT("'"&Sheets&"'!A:A"), A2))

Here, INDIRECT("'"&Sheets&"'!C:C") is the range to sum, and INDIRECT("'"&Sheets&"'!A:A") is the range with names (the criteria range).

You can add more criteria range / criteria pairs as needed.

Fill down.

Answer 3

Hi @Snow Lu MSFT,

Thank you, this works to an extent but since I have more than 15 sheets, it could get a little messy to add up sumif multiple times. Since I just shared a similar example, you could add another column lets say year as well for the names and have more rows. If year is 2022 then add up all marks for that name and for 2023 for the same name and so on.. Just a heads-up, since there are about 15 sheets (more will be added on a weekly basis), would need it to be friendly in that way as well. Tq

Answer 4

Thank you, @HansV MVP, seems a bit complicated for me. But will try to incorporate and understand them for future reference. Tq, will look up youtube for a better reference on this method you shared. Seems this will help to automate future sheets getting added as well. Tq

Answer 5

Why not combine all the data in a single data sheet? You already have a column that specifies the subject.

Having all data in one sheet would make the formulas much simpler.