Formula challenge: SUMIF from more sheets

posted Jan 10, 2014, 8:29 AM by Krisztina Szabó   [ updated Jun 12, 2014, 2:47 AM ]
Freddy: This is the moment. Well, dear, are you ready?
Inga: Yes, Doctor.
Freddy: Elevate me.
Inga: Now, right here?
Freddy: Yes, yes, raise the platform.
Inga: Oh, the platform, oh that, yeah, yes.


by The FrankensTeam

We had found a similar question in a forum, and started to think: could it be done using formulas? Well, if you are reading this post, then you know the answer is yes.

Let’s say you have this kind of data on some sheets:

And you have a list of sheet names - we created a name for it:

MySheets={“Company1”;“Company2”;“Company5”}

Can you write a formula to fill in this table on a separated Summary sheet?


If you would like to think, you can download our file with sample data. We publish the solution a little bit lower, so do not scroll down, but download the example without solution.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Solution

Formula for cell B4:
=SUM(SUMIF(INDIRECT(MySheets&"!$a$4:$a$100"),$A4,INDIRECT(MySheets&"!R4C"&COLUMN()&":R100C"&COLUMN(),0)))
to be array-entered and copy down and right.

It is not difficult, is it? INDIRECT could result an array of references for both the range and the sum range. SUMIF is able to “pair” these ranges and calculate the sum based on the criteria, then give back the result in array: one number for each sheets. That is why we have to use SUM, so to add up the sheet-results.
And one small thing could also be interesting: we used R1C1 reference style in the second INDIRECT, so the second argument is 0. This way it is easier to concatenate the reference from strings. (Please note, if you use non-english language Excel version, this formula may not work for you. You will have to replace the R and C letters with your local equivalents.)

You can download the file with the solution.

So we are finished.

Or not?
Well, you know our mind never stops thinking.
Maybe it is possible to create an array of sheet names using formulas?
Continue reading here.

Leave a comment