How to sum datas in various sheets in the same column?

[franlem2]

Hi,

It's quite complicated for me to explain. I have various sheets. In the same column I have in the cell the name (to make it simple let's say a or b, but there are about 15 different criterias, each sheet has dozens of value in each sheet) and in the cells below the values. I want to sum, everything that has the criteria a and everything thathas the criteria b, in an independent sheet. Please find enclosed an image below that shows what I want to do, taking into account that the same column in all thwese different sheets can have various values (for example in sheet1 I put 22 but it could be 22, below, 33, below , 123 etc..). In the example I obtain 22+487 = 509 for a and 12 + 789 = 801. How could I do it automatically? Any formula? I thought about index and sum.if but it not applyable (even with a CSE formula? ) It must be exactly this structure, it's not possible to put everything in column. Thanks for your help.

 

[Special-K99]

Take a look at or Google for 3d referencing/sum

http://excel3dreferencing.blogspot.com/

 

[franlem2]

Thank you but I still googled it before and don't find in the page the way to make the sum if indicated in my question. It's not a simple sum it's a sum if, but thanks however, you were the only one to answer me.

 

[RonB1111]

For a small number of sheets this would work, but it's probably too cumbersome for a large number of sheets. This assumes you have 4 sheets:

=IF(A3=Sheet1!D2,SUM(Sheet1!D2:D49),0)+IF(A3=Sheet2!D2,SUM(Sheet2!D2:D49),0)+IF(A3=Sheet3!D2,SUM(Sheet3!D2:D49),0)+IF(A3=Sheet4!D2,SUM(Sheet4!D2:D49),0)