Trouble with SUMPRODUCT

[edwardpestian]

I need to add to the following SUMPRODUCT formula.

=SUMPRODUCT(--(MOD(COLUMN($F5:$CO5),3)=0),$F5:$CO5)

This formula basically sums every 3rd column in the range F5:CO5. I
need it to sum every 3rd column in the range, only based on the date in
cell CY2. The date range is F3:CO3. The date range is the 3 columns
merged together however.

So the sum would only be up to and including the date in CY2.

Thanks.

ep

 

[Biff]

Hi!

So the sum would only be up to and including the date in CY2.

What do you mean by "up to and including" ?

That implies a date range (start date to end date) but you don't mention
what the start date is. You just mention cell CY2.

Here's the formula written to = CY2:

=SUMPRODUCT(--(MOD(COLUMN($F5:$CO5),3)=0),--(F3:CO3=CY2),$F5:$CO5)

If you have a date range:

CY1 = start date
CY2 = end date

=SUMPRODUCT(--(MOD(COLUMN($F5:$CO5),3)=0),--(F3:CO3>=CY1),--(F3:CO3<=CY2),$F5:$CO5)

Biff

"edwardpestian" <[email protected]>
wrote in message

 

[duane]

"The date range is F3:CO3. The date range is the 3 columns merge
together however."

Could you explain this a little better. Is the date made up o
F3&G3&H3

 

[edwardpestian]

Something strange is happening. When I move one column to the right.
In order to sum the second column in the range, its summing the entire
range instead of the dates up to and including the date in cell CY2.

=SUMPRODUCT(--(MOD(COLUMN($F5:$CO5),3)=1),--($F$3:$CO$3<=$CY$2),$F5:$CO5)

Thanks again.

ep

 

[Biff]

"edwardpestian" <[email protected]>
wrote in message

Something strange is happening. When I move one column to the right.
In order to sum the second column in the range, its summing the entire
range instead of the dates up to and including the date in cell CY2.

=SUMPRODUCT(--(MOD(COLUMN($F5:$CO5),3)=1),--($F$3:$CO$3<=$CY$2),$F5:$CO5)

That's because of your merged cells. Moving one column to the right (by
changing the MOD to = 1), now the formula is referring to G5:CO5 and G3:CO3.
G3 and all cells in row 3 within the range that meet the MOD condition are
empty and those cells evaluate to 0 and 0 <= CY2. Even though G3 is merged
with F3 and H3 it can still be referenced as a separate cell. Only the top
left cell in a merged group contains the value.

You can try this:

=SUMPRODUCT(--(MOD(COLUMN($F5:$CO5),3)=1),--(OFFSET(F3:CO3,,-1)<=CY2),$F5:$CO5)

Personally, I would unmerge the cells and put dates in all cells of the date
row. Merged cells usually do nothing but cause problems!

Biff

 

[edwardpestian]

Thanks Biff,

Understanding that the top left hand cell contains the merged data
clarifies why the same formula will not work. As you indicated in your
suggestion, the best way would be to unmerge the cells and put the date
in all three cells. I simple changed the font color to match the
background color on the left and right cell and it looks as it did.

ep

 

[Biff]

You made the right decision. Thanks for the feedback!

Biff

"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...