Add value from multiple sheets

pranamu

New Member
Joined
Nov 2, 2011
Messages
49
Dear,

Can anyone please help me to add value of below multiple sheets in summary sheets with the formula of =sumif(indirect(....?
I want to add these sheets value in one summary sheet which will have exact format. What formula can be used in summary sheet at B2 cell.
Your support will be highly appreciated.

Sheet1
A B C D
Class​
Jan​
Feb​
Mar​
Class 1​
Class 2​
Class 3​
Sheet 2​
Class​
Jan​
Feb​
Mar​
Class 1​
Class 2​
Class 3​
Sheet 3​
Class​
Jan​
Feb​
Mar​
Class 1​
Class 2​
Class 3​
Sheet 4​
Class​
Jan​
Feb​
Mar​
Class 1​
Class 2​
Class 3​
Sheet 5​
Class​
Jan​
Feb​
Class 1​
 

Attachments

  • sumif, indirect.JPG
    sumif, indirect.JPG
    36 KB · Views: 6

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
Try:

Book3
ABCDEF
1ClassJanFebMarSheets
2Class 11026Sheet1
3Class 22102Sheet2
4Class 36210Sheet3
5Sheet4
Sheet5
Cell Formulas
RangeFormula
B2:D4B2=SUMPRODUCT(SUMIFS(INDIRECT("'"&$F$2:$F$5&"'!"&CELL("address",B$2)&":"&CELL("address",B$4)),$A$2:$A$4,$A2))
 

pranamu

New Member
Joined
Nov 2, 2011
Messages
49
Hi Eric,

I tried but it didn't work out. Do I need to define the name of each sheet?
What does that "address" denote? sorry I am poor on this.
Sheet 1, 2 and 3 has data on it with same format as Summary sheet.

excel.JPG
 

Attachments

  • excel.JPG
    excel.JPG
    45.3 KB · Views: 2

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
Hard to tell for sure from your screen print, but it looks like your sheet names in F2:F4 have a space between "Sheet" and the number, whereas the actual sheet names do not have a space. Check that first. They should match exactly. Also, your formula references F2:F5, and F5 is empty. You need to make the range the same size as the range holding the sheet names.

The "address" parameter in the CELL function just returns a text version of the cell address, so CELL("address", $B2) just returns "$B$2". Then you can use this in the INDIRECT function to build the text address of the range you want to search. There is actually an ADDRESS function, but it doesn't work in this type of formula.
 

pranamu

New Member
Joined
Nov 2, 2011
Messages
49

ADVERTISEMENT

Thanks Eric. It's worked amazingly after amending suggestion given by you.
I have many classes in some sheets and some has only 3 classes. Let's say, I have class 4, 5 7 and 8 values on sheet1, but I have only value of class 1, 2 and 3 on sheet2. And, they all are in descending order on every sheets. like, cell A2 has Class 1 on sheet 1 but sheet2 has Class 5 on cell A2. So, with above formula input the value of B$2 cell is added from every sheets even though B$2 refer to not for class 1 on some sheets. I want to add the value according to class.

I know that my initial sheets was prepared in an ascending order so you suggested me above formula. I apologize for that
Can you please suggest how can I add value from different sheets according to exact match of classes. The formula should add values according to class and their values availability on different sheets.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,739
I assumed the list would be the same on each sheet, so to simplify things, I used the conditional range from the Summary sheet. Since that's not the case, we can just use another INDIRECT for the conditional range, like so:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$F$2:$F$5&"'!"&CELL("address",B$2)&":"&CELL("address",B$20)),INDIRECT("'"&$F$2:$F$5&"'!$A$2:$A$20"),$A2))

Put that in B2 and drag down and across.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,906
Messages
5,621,577
Members
415,846
Latest member
nigeywigey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top