sumifs over varing sheets

Dear heros,

I'm trying to sum over several sheets. The numbers are only summed if they are in a certain category. The sheet number and the names are variable.
The sheet names are written in F1:F7. However, it might be the case that e.g. F6 and F7 are empty. The number to be summed is written in A2, its category in A1. I only sum if the category is the same as the one in C2.

I have:

Please Login or Register  to view this content.

Now when F6 and F7 are empty I get #REF. What can I do to ignore the empty cells?

Thank you heros for saving me,
hänschendampf

Hi Kaper,

Thanks for the fast reply. It works very well for the little example. However the final application will have around 80 sheets. Do you think there is a faster way to do it?

I attached the example.

Cheers
hänschendampf

pgc01 from mrexcel forum got the answer:

Filter the empty cells, like:

=SUM(IF(F1:F7<>"";SUMIF())

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Jup, this works as well. Thanks Tony

Can you suggest a tutorial for array formulas? I encountered them several times but never managed to entirely understand it.

Thanks a lot :-)

mmmh, I have a new problem. I want to have the categories of the sheets in G1:G7 (the row next to the sheet names)

So I take the code:

Please Login or Register  to view this content.

But now the sum over all sheets is calculated if G1 is the same as A5, no matter what's in G2:G7.

Any idea?

First I had this:

Please Login or Register  to view this content.

Here I checked the "category" which was written for each sheet in A1 is corresponding to $A$5 in the first sheet.

Now I don't want to have the "categories" in each sheet at A1 but rather in the range G1:G7 in the first sheet.

Please Login or Register  to view this content.

This however seems not to work. It gives me fancy numbers.

@Tony Valko

I very much appreciate your help. But I just realized that this way is leading nowhere. I will have to rearrange my dataset.

Thanks Tony.

Hi everybody

I have a little attachment, which hopefully helps to understand my problem:

In E1:E10 the tables that I want to include are listed. In A1 I sum all the A1's from the tables that are listed in E1:E10. Later I want to sum all the A2's in A2, B1's in B1 and so on.

As you see for summing A1 it all works well. The problem is, when I add a number in any sheet in column A below A1 (e.g. first!A2 = 2) it gets added up which is not my intention. It would be so helpful if someone could dig in my formula to find the error.

really? is it possible that there is a mistake in translation? I'm using the german version...
here's the german code:

Please Login or Register  to view this content.

In cell A1 of MAIN I want to sum all the cells A1 from the sheets in column E.

So e.g.:
MAIN!A1 = first!A1 + second!A1 + third!A1 + fourthA1
In the attached example the result should be 10.

Hi Tony, thanks for your post. This works just great!

So I think in English you would have to change "z" into "r" and "s" into "c". In the indirect function I chose the R1C1 style since I want to drag the formula to other cells later. It works well. I don't even try to translate ;-):

Please Login or Register  to view this content.