SUMPRODUCT Help

[inkserious]

I have the following formula that works as expected; however, I need to modify the formula to work for a change in the first array. Currently, A1:AE1 has dates of the month (8/1/2011, 8/2/2011, etc). C1 has a specific date. So for all dates less than or equal to the date in C1, sum the product of the values in the range A2:AE2 and Sheet2!A1:A31. I need to change the dates to every other cell which changes the date range to A1:BJ1; however, the values will remain in their current ranges.

{=SUMPRODUCT(--($A$1:$AE$1<=$C$1),$A$2:$AE$2,TRANSPOSE(Sheet2!$A$1:$A$31))}

Thanks for any help anyone can provide.

 

[Gavona]

Sumproduct needs a contiguous range so I don't think this ispossible.
Your data sheet seems really oddly organised. You seem to imply that the date in BJ relates to data in AE.
I would consider if it is feasible to re-organise the worksheet.
Possibly simply insert a row above 1 and populate it with every other value from what is now row 2. Then your original formula would work.

There are probably a few ways to populate the inserted row. One is:
=OFFSET($A$1,1,COLUMN(A2)*2-2)

Gavin

 

[SkipVought]

hi,

What you need is a formula in your date range to select the desired date for the SUMPRODUCT to use.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Yes you are correct the data is oddly organized. What I did was insert an additional row and used it to maintain a contiguous range.

Thanks for the help.

Regards,

-ep