Multiple sumproducts on columns

[Cunning]

Hi,
I have a spreadsheet where I need to multiply the 1st column by the 2nd column + 3rd column by the 4th + 5th by 6th etc... except for a range between S and EX... I'd rather avoid the long convuluted =(S11*T11)+(U11*V11)+.....(EW11*EX11)

Is there a neater way of doing this?

TIA

 

[jasonb75]

Try

=SUMPRODUCT((ISODD(COLUMN(S11:EW11))*S11:EW11),(ISEVEN(COLUMN(T11:EX11))*T11:EX11))

 

[Cunning]

Unfortunately not. Not quite sure how this is working but it's returning a figure of 1254 instead of 174.

 

[jasonb75]

It works for me, have you double checked your data for any stray values you may have missed?

Note, you have numeric data such as dates in that range they would be included in the calcultion, although if that was the case I would have expected the resulting value to be much higher.

 

[Cunning]

I dropped the range down to run a check on it.

Column

A B C D

1 90 12 7


=1*90+12*7 = 174

But the other formula is returning 1254

Not sure why?

 

[jasonb75]

Did you put the formula in that range? If you have and you're using circular referecnes then that could explain it.

 

[Cunning]

=SUMPRODUCT((ISODD(COLUMN(A1:C1))*A1:C1),(ISEVEN(COLUMN(B1:D1))*B1:D1))

is the formula I've used

 

[jasonb75]

Sorry, badly worded, what I meant was did you put the original formula in a cell between S11 and EX11?

 

[Cunning]

No. It was in FB

 

[jasonb75]

Any values in the range hidden by conditional formatting?

What does =COUNT(S11:EX11) return?

If the other cells are not empty and show 0 then use =COUNTIF(S11:EX11,"<>0") instead?

Based on your example above, one of these shoule return a result of 4, if not you have extra values somewhere.

Try Alt M V then press evaluate 8 times and check the resulting arrays for the positions of any unexpected values.