Attempting SUMIF across worksheets using INDIRECT and SUMPRODUCT

[Rig88]

Hi,

I'm trying to SUM cell J4 across a range of worksheets but only SUM if a static date, K4, on the worksheets are equals too or less than today's date. This is my current attempt at getting this formula to work;

=SUMPRODUCT(SUMIF(INDIRECT("'"&O7:O20&"'!K4"),"=<"&I3,INDIRECT("'"&O7:O20&"'!J4")))

I've listed my worksheet names in O7:O20 and I3 has "=TODAY()" in, on the same worksheet I'm trying to sum up these values. I should get a value of £1460.04 back from at least 1 of the cells if this formula was working correctly, but I'm getting a value of £0.00.

I'm currently using Window 7 and Office 2010.

 

[jorismoerings]

Hi,

2 things to take into account:
1. change your formula to this: =SUMPRODUCT(SUMIF(INDIRECT("'"&O7:O20&"'!K4"),"<="&I3,INDIRECT("'"&O7:O20&"'!J4"))) - beware of the small change in the criteria definition.
2. your formula will return a #REF error if the sheets listed in the range O7:O20 will not exist.

 

[Rig88]

Hi,

2 things to take into account:
1. change your formula to this: =SUMPRODUCT(SUMIF(INDIRECT("'"&O7:O20&"'!K4"),"<="&I3,INDIRECT("'"&O7:O20&"'!J4"))) - beware of the small change in the criteria definition.
2. your formula will return a #REF error if the sheets listed in the range O7:O20 will not exist.

Oh my, I can't believe that is all it took to get this formula working, I feel a little bit of shame for overlooking something so simple.

Thank you very much for your time though, I really appreciate it.

 

[jorismoerings]

Hi,

As usually "the devil is in the details".
Glad i could help.