Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 31 Times in 31 Posts
    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. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks HansV. That's exactly what I was looking for.

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    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. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The formula does what you need. It does multiply and add as you require.

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Ah - I have it now!

    I had inserted an extra column to the left of the range I am calculating.
    Therefore, instead of:
    =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
  •