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

Agree with MVP.

Consolidate Multiple Excel Sheets Using Power Query Append | MyExcelOnline
How to Combine Excel Sheets with Power Query - Xelplus - Leila Gharani

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Answer 2

Hi,

Follow the steps in this video to append data from multiple worksheets

Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube

Thereafter, you can build a Pivot Table.

Answer 3

https://techcommunity.microsoft.com/t5/excel/vstack-indirect-and-byrow-to-pull-in-data-from-many-sheets/m-p/3771657#M185371