Add value from multiple sheets

pranamu

New Member
Joined
Nov 2, 2011
Messages
47
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

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,750
Try:

Book3
ABCDEF
1ClassJanFebMarSheets
2Class 1
10
2
6
Sheet1
3Class 2
2
10
2
Sheet2
4Class 3
6
2
10
Sheet3
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
47
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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,750
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
47
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
9,750
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,099,055
Messages
5,466,312
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top