3D Sumif Problem

[Mike Blackman]

Hi All,

I have the following Sumif running from sheet 700069:

=SUMIF('700069'!$B:$B,$B4,'700069'!$AI:$AI)

And I now want it to span 5 sheets 700069 thru to 700073.

I figured it would either be:

=SUMIF('700069:700073'!$B:$B,$B4,'700069:700073'!$AI:$AI)

or

=SUMIF('700069':'700073'!$B:$B,$B4,'700069':700073'!$AI:$AI)

but neither work.

I have the following formula but its very long and ugly:

=SUMIF('700069'!$B:$B,$B3,'700069'!$AI:$AI)+SUMIF('700070'!$B:$B,$B3,'700070'!$AI:$AI)+SUMIF('700071'!$B:$B,$B3,'700071'!$AI:$AI)+SUMIF('700072'!$B:$B,$B3,'700072'!$AI:$AI)+SUMIF('700073'!$B:$B,$B3,'700073'!$AI:$AI)

Can anybody help?

 

[Jonmo1]

With a single formula, that's probably going to be as good as it's going to get...The 3D sheet reference I believe only works with select simple functions like SUM.

however, with multiple formulas it could be done like..

Say in G1 for example, put this

=SUMIF(INDIRECT("'7000" & Row()+68 & "'!$B:$B"),$B4,INDIRECT("'7000" & Row()+68 & "'!$AI:$AI"))

Then fill down to G5

That will give you the same as if you manually typed
G1 =SUMIF('700069'!$B:$B,$B4,'700069'!$AI:$AI)
G2 =SUMIF('700070'!$B:$B,$B4,'700070'!$AI:$AI)
G3 =SUMIF('700070'!$B:$B,$B4,'700071'!$AI:$AI)
G4 =SUMIF('700072'!$B:$B,$B4,'700072'!$AI:$AI)
G5 =SUMIF('700073'!$B:$B,$B4,'700073'!$AI:$AI)

Then you can use =SUM(G1:G5)

 

[Lewiy]

=SUMIF('700069'!$B:$B,$B3,'700069'!$AI:$AI)+SUMIF('700070'!$B:$B,$B3,'700070'!$AI:$AI)+SUMIF('700071'!$B:$B,$B3,'700071'!$AI:$AI)+SUMIF('700072'!$B:$B,$B3,'700072'!$AI:$AI)+SUMIF('700073'!$B:$B,$B3,'700073'!$AI:$AI)

I think this one's what you're gonna have to use I'm afraid.

 

[Mike Blackman]

Thank you both.

Another (im)possability is this:

A B C
1 Name Cost Centre Days Booked
2 Person A 700070 Required Formula
3 Person B 700069 Required Formula
4 Person C 700073 Required Formula


Can you use the value of cell B2 to populate the sheet part of the formula?

eg. =SUMIF('sheet(B2)'!$B:$B,$B4,'sheet(B2)'!$AI:$AI)

 

[Domenic]

Try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&{700069,700070,700071,700072,700073}&"'!B:B"),$B4,INDIRECT("'"&{700069,700070,700071,700072,700073}&"'!AI:AI")))

or

=SUMPRODUCT(SUMIF(INDIRECT("'7000"&{69,70,71,72,73}&"'!B:B"),$B4,INDIRECT("'7000"&{69,70,71,72,73}&"'!AI:AI")))

Hope this helps!

 

[Mike Blackman]

Hi Domenic

Thats incrediible, works perfectly. I'd never of thought of using SUMPRODUCT (partly because i've been struggling to get me head round it) and I haven't come across INDIRECT before.

Many many thanks

 

[Brian from Maui]

Another alternative is to create a named range for your sheets and use,

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A1:A10"),"a",INDIRECT("'"&list&"'!B1:B10")))

Where list is the name given to the named range housing the sheet names