Excel 2007 : Alternative for using SUMIF with a 3D Reference

[qaliq]

Morning,

Is there a non VBA alternative to being able to effectively create a SUMIF function using a 3d reference?

Basically, I have a number of sheets (15 at the moment but the idea is people will be able to add/take them away when they like), that are arranged such that there is a tab on the left of the first called "Start" and one at the right of the last called "End". They are called loc_a, loc_b,loc_c etc. Each location is then allocated to an area (Area1, Area2, Area3) and the area the location is allocated to is specified in cell E3 on the loc sheets. What I am trying to do is have three area summaries which sum the values from the individual loc sheets if their area is equal to the summary area (the name of the summary area is contained in M3 on the summary sheets).

What I started with was =SUMIF(Start:Finish!$E$3,Summary_Area1!$M$3,Start:Finish!G18) but (as I have now found out...) the SUMIF function does not support 3d formulas. I then tried to write a SUMPRODUCT formula.... =SUMPRODUCT(SUMIF(INDIRECT("'"&Start:Finish&"'!$E$3"),$M$3,INDIRECT("'"&Start:Finish&"'!G18"))) but I keep getting an error with that. I'm trying to shy away from using a macro solution if possible.

I cannot attach the spreadsheet but I have created a very simplified example workbook to illustrate what I mean (ignore the formatting, the cells are in certain places so it matches my actual sheet!).

Any help would be much appreciated.

Thanks,

Alice
example - 3d SUMIF.xls

[NBVC]

If you don't want to use VBA, then you will need to create a list of the sheetnames to include in the sheet itself, then use your Sumproduct formula to indirectly refer to that list.

e.g. if you create a list of sheets and call it MyList, then you would write:

=SUMPRODUCT(SUMIF(INDIRECT("'"&MyList&"'!$E$3"),$M$3,INDIRECT("'"&MyList&"'!G18")))

You can make this list dynamic, so if you add more sheets to the list, the named range will self-adjust.

So say you enter the list starting in O2 of the Summary sheet, then you would go to Formulas|Define Name, and enter MyList for the name and formula:

=Summary_Area3!$O$2:INDEX(Summary_Area3!$O:$O,MATCH(REPT("z",255),Summary_Area3!$O:$O))

for the source.

Alternatively, you can enter a formula on each individual sheet in a new cell like: =IF(E3=Summary_Area3!M3,G18,0)

Then sum that cell across the sheets with =SUM('Start:Finish'!O9) where O9 has above IF formula in each sheet.

[qaliq]

Yeah I had thought about naming a range, the only problem is that the spreadsheet won't be used by me and I want to have a loc and an area template tab so users can set up new areas/locs automatically. If they then have to start defining named ranges people won't do it and I'll just have to set them up for them anyway.

With the IF statement, I don't understand how that would work to distinguish different areas. If i wrote that one, surely for my sums in Area 1 and Area 2 would be zero. Please forgive me if I didn't understand what you're trying to say!

I think I’ve come up with a simple solution, I am just going to set up a number of blank tabs "area 1 start" "area 1 end "area 2 start" "area 2 end" etc. etc. and then have people drag the loc tabs in between certain ones so then I can just use a simple sum 3d reference in my summary sheets. Not ideal, but it works for the audience that will be using the spreadsheet.

Thanks,

Alice