# Thread: Formula rationalisation / simplification

1. How can I simplify the following formula that's in cell M1:

=(A1*B1)+(C1*D1)+(D1*F1)...+(K1*L1)

This is itself a simplification of my actual requirement, but it provides the overall idea.

I have multiple pairs of columns, the sum of each pair needs to be added together.

Each pair of columns contains a quantity and a price, from which I need a total dollar value at the end of the row.

Ideally, I would restructure this layout, but unfortunately that's not an option.

I had thought that maybe a solution using SUMPRODUCT might work, but I can hit on the right answer.

Any assistance is much appreciated.

TIA

2. I really think it would be worth the trouble to change the layout of the data, but you could use this SUMPRODUCT formula:

=SUMPRODUCT(A1:K1,B1:L1,MOD(COLUMN(A1:K1),2))

The third argument MOD(COLUMN(A1:K1),2) is TRUE=1 for odd-numbered columns A, C, E, ... and FALSE=0 for even-numbered columns B, D, F, ...

3. Thanks HansV. That's exactly what I was looking for.

4. Hmmm ...

I just revisited this after thinking I had it working ok, but -

it appears the formula is just adding every second value in the range.

What I really need to do is to multiply every pair of columns, then sum their results.
ie. =(A1*B1)+(C1*D1)+(E1*F1)...+(K1*L1)

5. The formula does what you need. It does multiply and add as you require.

6. Ah - I have it now!

I had inserted an extra column to the left of the range I am calculating.
=SUMPRODUCT(A1:K1,B1:L1,MOD(COLUMN(A1:K1),2))

I had to change the range for the MOD to be the second range:
=SUMPRODUCT(A1:K1,B1:L1,MOD(COLUMN(B1:L1),2))

Previously I was starting on an odd numbered column, now it's an even numbered column.

Thanks mdbct!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•