I've found varying formulas that seem to work well for other people but are always one step short of doing what I want to do.
I've toyed with sumif, but would prefer sumproduct because the end user has 2003 and while it's always fun to say "Get with the times!"... well, you know.
Here's the quandry:
On the first worksheet, every two columns there is a beg date and an end date so I need the final formula to reference cells with the date format 2009-07-20. (I need to run it down the "line" so to speak because there are way too many cells.)
H2= Beg Date
I2= End Date
'FW Hrs 2009'!K2:K582 is the sheet/location of the date criteria.
If the date is equal to or between H2 & I2, I need to sum the values from the corresponding cells in columns v2:v582, y2:y582, and ab2:ab582.
This the formula I've been trying to use, needless to say, it doesn't work:
=SUMPRODUCT(--'FW Hrs 2009'!$K$2:$K$582>=H2)--('FW Hrs 2009'!$K$2:$K$582<=I2)*('FW Hrs 2009'!$V$2:$V$582)+('FW Hrs 2009'!$Y$2:$Y$582)+('FW Hrs 2009'!$AB$2:$AB$582)
On another note, it seems like some =sumproducts are to be closed as array functions and some are just normal functions. What gives?
I have literally spent all day trying different variations of this. I've been close a few times to just pulling the data using an array on a separate sheet, and then summing the values, but to create slightly different array formulas for all 52 weeks of the year... would be processor intensive.
Thanks in advance for the help.
I've toyed with sumif, but would prefer sumproduct because the end user has 2003 and while it's always fun to say "Get with the times!"... well, you know.
Here's the quandry:
On the first worksheet, every two columns there is a beg date and an end date so I need the final formula to reference cells with the date format 2009-07-20. (I need to run it down the "line" so to speak because there are way too many cells.)
H2= Beg Date
I2= End Date
'FW Hrs 2009'!K2:K582 is the sheet/location of the date criteria.
If the date is equal to or between H2 & I2, I need to sum the values from the corresponding cells in columns v2:v582, y2:y582, and ab2:ab582.
This the formula I've been trying to use, needless to say, it doesn't work:
=SUMPRODUCT(--'FW Hrs 2009'!$K$2:$K$582>=H2)--('FW Hrs 2009'!$K$2:$K$582<=I2)*('FW Hrs 2009'!$V$2:$V$582)+('FW Hrs 2009'!$Y$2:$Y$582)+('FW Hrs 2009'!$AB$2:$AB$582)
On another note, it seems like some =sumproducts are to be closed as array functions and some are just normal functions. What gives?
I have literally spent all day trying different variations of this. I've been close a few times to just pulling the data using an array on a separate sheet, and then summing the values, but to create slightly different array formulas for all 52 weeks of the year... would be processor intensive.
Thanks in advance for the help.