Add value from multiple sheets

pranamu

Board Regular
Joined
Nov 2, 2011
Messages
56
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: 7

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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))
 
Upvote 0
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: 4
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top