+ Reply to Thread
Results 1 to 7 of 7

Sum column cells across multiple colums

  1. #1
    Registered User
    Join Date
    12-21-2010
    Location
    england
    MS-Off Ver
    Excel Office 365
    Posts
    33

    Sum column cells across multiple colums

    Hello,

    I have a table in excel, series of columns with Prices per weight and then each row shows someones post usage and quantity per weight band.

    what im trying to do is, put a formula in so that at the end of the row, display the sections combined postage.

    Tried a few different variations but cant find an automatic way to create a formula rather than doing a long manual one i.e =E5*E7+F5*F7+G5*G7+H5*H7+I5*I7+J5 all the way to BN

    The next row down would then be the same price row (4) and quantity row change from 5 to 6. Thoguht if i had one that worked , could just autofill down so that the row changed to 7,8,9 etc.

    Thought I was on the right lines like this but obviously not

    SUM(MMULT(E3:E5,H3:H5))

    SUMPRODUCT(E3:E5,H1:H5)
    Attached Images Attached Images

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum column cells across multiple colums

    sum product should work but the ranges need to be the same length

    =E5*E7+F5*F7+G5*G7+H5*H7+I5*I7
    would be
    sumproduct(e5:I5,e7:I7)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    12-21-2010
    Location
    england
    MS-Off Ver
    Excel Office 365
    Posts
    33

    Re: Sum column cells across multiple colums

    Ive adjusted the references to suit my colums, doesnt seem to add things up correct
    Column E-I should total = £8.05
    Doesnt seem to be calculating column E total and adding it in

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum column cells across multiple colums

    cant possibly tell as
    1, images are no good and
    2 your image shows nothing anyway
    Attach a sample workbook.



    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Registered User
    Join Date
    12-21-2010
    Location
    england
    MS-Off Ver
    Excel Office 365
    Posts
    33

    Re: Sum column cells across multiple colums

    attached the page im working on with the formula. i managed to get it working where i can autofill it down for other rows, they add up the first calculation ok. just E5 doesnt work
    Attached Files Attached Files

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum column cells across multiple colums

    change to
    =SUMPRODUCT(--E$3:G$3,--E5:G5) or
    =SUMPRODUCT(E$3:G$3*E5:G5)
    e5 ie 6 is a number formatted as text so sumproduct is ignoring it by using * or -- you coerce it back to a number
    or re-enter the value in e5

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Sum column cells across multiple colums

    The formula in ok but Cell E5 is not in number format
    try this
    =SUMPRODUCT(E$3:G$3,--(E5:G5))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula required for comparing multiple colums to multiple colums
    By Pringgles in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-30-2012, 07:23 AM
  2. [SOLVED] Way to distribute one column of information into multiple colums?
    By Beattie in forum Excel General
    Replies: 3
    Last Post: 03-27-2012, 02:37 PM
  3. Lookup column for value, then several colums for multiple rows
    By euch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-19-2011, 03:21 PM
  4. Multiple Transpose from Same column to different rows and colums.
    By marc5354 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-20-2010, 06:14 PM
  5. Excel 2007 : multiple colums under a single column
    By RobotChicken in forum Excel General
    Replies: 3
    Last Post: 12-15-2009, 01:34 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1