Share via

Sumif over multiple sheets

Anonymous
2023-07-24T17:57:26+00:00

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
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-07-24T18:08:14+00:00

    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.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2023-07-24T18:07:16+00:00

    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)

    Image

    ImageImage

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

    Image

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

    3 people found this answer helpful.
    0 comments
  3. Anonymous
    2023-07-25T00:24:57+00:00

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2023-07-24T18:26:05+00:00

    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

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2023-07-24T18:23:21+00:00

    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

    0 comments