Sumproduct with Indirect formula.

[Carley]

Good Afternoon

I have a worksheet which contains telephony information

At present I have three tabs and then a summary tab.

One tab for 2011, one for 2012 and one for 2013

I have a formula for each year which the team leaders can enter a start and end date and get the telephony information back

For 2013 =SUMPRODUCT(--('2013 Data'!$A$4:$A$275>=$E$6)*('2013 Data'!$A$4:$A$275<=$F$6)*('2013 Data'!$B$4:$B$275))

For 2012 =SUMPRODUCT(--('2012 Data'!$A$4:$A$277>=$L$6)*('2012 Data'!$A$4:$A$277<=$M$6)*('2012 Data'!B4:B277))


Now the team leaders would like a formula in which they can look at all three years,

I have tried a couple of formulas that I found on this forum but they don't like the >= or <= when looking at the date these are:


=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!$A$4:$A$277"),<=e6,INDIRECT("'"&tabs&"'!$A$4:$A$277"),<=f6),INDIRECT("'"&tabs&"'!$B$4:$B$277"))

and

=SUMPRODUCT((THREED('2012 Data':'2013 Data'!$A$4:$A$277<=e6)*(THREED('2012 Data':'2013 Data'!$A$4:$A$277>=f6)*(THREED('2012 Data':'2013 Data'! $B$4:$B$277)))


Can anyone help please?

 

[Absolution]

I get this issue alot with my SUMIfs.. Easily fixed by putting it into the following format:


"<="&cellref


So..


=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!$A$4:$A$277"),"<="&e6,INDIRECT("'"&tabs&"'!$A$4:$A$277"),"<="&f6),INDIRECT("'"&tabs&"'!$B$4:$B$277"))


I can't look at the 2nd one yet as I can' work out what THREED does.


Let me know if that works for you,

Absol

 

[Luke M]

Ditto to what Absolution said, I'm not sure what the THREED function is. But the answer will be similar, need to enclose the logic operator in quotation marks.

However, it looks like you might be trying to do a 3D reference (referencing multiple sheets) in the 2nd formula. Only certain functions support 3D reference, and SUMIF and SUMPRODUCT are not in that list, unfortunately.

 

[Hui]

Threed was an Excel addin function that added a lot of 3D flexibility to excel

It was supplied as part of the MoreFunc Excel Addin


I don't believe it is available anymore as it wasn't ported well to 32 bit PC's and most of us are now on 64 bit PC's

 

[Luke M]

Thanks for the info Hui. Now that name makes more sense. =)

 

[SirJB7]

@Hui

Hi!

Thanks for the information, I neither knew it. Maybe I was very young, just a little kid, when that function existed.

Regards!

 

[Carley]

Good Morning All,


Thanks for the feedback.


I have had to accept defeat and put all years data on one worksheet for the information to be populated into the summary report, due to sumproduct not allowing the 3D referencing.

 

[Haseeb A]

Hello Carley,


Assuming you already have a range with all sheet name entered & named as 'Tabs'


If you are using Excel 2007 or later you could use SUMIFS;


=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tabs&"'!B:B"),INDIRECT("'"&Tabs&"'!A:A"),">="&StartDate,INDIRECT("'"&Tabs&"'!A:A"),"<="&EndDate))


Or with SUMIF which will work in all versions;


=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!A:A"),">="&StartDate,INDIRECT("'"&Tabs&"'!B:B"))-SUMIF(INDIRECT("'"&Tabs&"'!A:A"),">"&EndDate,INDIRECT("'"&Tabs&"'!B:B")))


Or you can use the below shorter version;


=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!A:A"),">="&IF({1,0},StartDate,EndDate+1),INDIRECT("'"&Tabs&"'!B:B"))*{1,-1})


Hope this helps;

Haseeb