Solved: Sum or Sumproduct help

[lynnnow]

Hi Guys,

I've got a sheet with two columns per date, i.e., Mins and WC.

I need to horizontally sum the Mins and WC separately at end of the table.

The SUM formula allows for only 30 items. I've been reading the SUMPRODUCT article by XLD but still need to get my head around the subject.

Alternatively, I can use the "+" symbol, but considering the difference in the number of days of each month, updating the formula and maybe even missing out a column can lead to a wrong result. Will SUMPRODUCT work for this? Is there any other way?

I'm using Excel 2003 on Win XP.

I'm attaching some sample data.

Thanks in advance.

Lincoln

[Bob Phillips]

Use

=SUMPRODUCT(--(MOD(COLUMN($B4:$BK4),2)=0),$B4:$BK4)

and

=SUMPRODUCT(--(MOD(COLUMN($B4:$BK4),2)=1),$B4:$BK4)

[Aflatoon]

In BL4:
=SUMIF($B$3:$BK$3,BL$3,$B4:$BK4)
then copy across and down.

[lynnnow]

Absolutely fabulous answers, both of you!!!

Bob, I'm stunned with your sumproduct formula. Please explain how it works. I tried stepping thru the formula, but can't seem to understand how the formula works.

[Bob Phillips]

Basically, my formula looks at each column and checks whether it is an even number column MOD(COLUMN($B4:$BK4),2)=0, or an odd numbered column MOD(COLUMN($B4:$BK4),2)=1.

That part of the formula will return an array of TRUE,FALSE,TRUE,FALSE,... and FALSE,TRUE,FALSE,TRUE,... (note the difference), which is converted to arrays of 1,0,1,0,... and 0,1,0,1,... by the double unary, --. This is then multiplied by the actual values, so it is just adding up every other value.

[lynnnow]

Thanks Bob, I had understood the column part, the unary bit threw me off and couldn't make sense of the rest. Thank you once again.