Trouble with SUMPRODUCT
 

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=557980

Trouble with SUMPRODUCT
 

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"
wrote in message
news:edwardpestian.2aej8n_1151984403.7471@excelfor um-nospam.com...

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=557980

Trouble with SUMPRODUCT
 

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

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=557980

Trouble with SUMPRODUCT
 

Yes, the date range is F3,G3,H3 merged together. The cell containing
the date - CY2 would for example contain 06/05/2006. So the formula
would sum every 3rd column; starting with the first column in the range
F5:CO5 who's date is less than or equal to the date 06/05/2006.

Biff, your formula worked except I had to add a less than sign. <=CY2.

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

Thanks fellas.

ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=557980

Trouble with SUMPRODUCT
 

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=557980

Trouble with SUMPRODUCT
 

"edwardpestian"
wrote in message
news:edwardpestian.2aemhd_1151988602.9131@excelfor um-nospam.com...

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

Trouble with SUMPRODUCT
 

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=557980

Trouble with SUMPRODUCT
 

You made the right decision. Thanks for the feedback!

Biff

"edwardpestian"
wrote in message
news:edwardpestian.2agizz_1152077403.3754@excelfor um-nospam.com...

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=557980