Closed Thread
Results 1 to 9 of 9

Sum three columns with one criteria

  1. #1
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    173

    Sum three columns with one criteria

    Hello,

    I am looking for a sum formula that can get me the sum of three columns with one criteria. I tried a sumproduct, but not sure I have the formula right- here it is:

    Please Login or Register  to view this content.
    Workbook attached - any ideas? I do not believe a sumif will work since I want the sum range to be multiple columns. Ideally a sumproduct would be best to keep in one formula I think. Or if there is another sum formula that works I am here for it.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Sum three columns with one criteria

    are you using MS365? I see you have a SPILL result in your file...

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Sum three columns with one criteria

    Use this in B3:
    =SUM(((A3=book2!$A$2:$A$22)*(book2!$H$2:$J$22)))

    Your formula is also good, it was missing parenthesis:
    =SUMPRODUCT((book2!A:A=book1!A3)*(book2!H:J))
    Last edited by Limor_OP; 02-11-2021 at 04:19 PM.

  4. #4
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    173

    Re: Sum three columns with one criteria

    @Belinda200 thanks! refined my sumproduct formula and it worked!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Sum three columns with one criteria

    Please update your forum profile from 2016 to 365.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    04-17-2021
    Location
    Capetown
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Sum three columns with one criteria

    Hello There

    I am trying to use this formula but its not working.

    I get spill when not using parenthasis

    Im getting a value sign when I close them.

    Sumproduct looks perfect ahve checked it againa and again

    Thanks

    See

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Sum three columns with one criteria

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  8. #8
    Registered User
    Join Date
    04-17-2021
    Location
    Capetown
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Sum three columns with one criteria

    Ok thanks ill give that a try. Im no "Hijacker"

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Sum three columns with one criteria

    Yes, you are - see our rules:

    4. Do not post a new help request in an existing thread. (B)
    Please do as you have been asked. Thanks.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 09-02-2019, 04:14 PM
  2. Replies: 5
    Last Post: 04-15-2019, 09:07 AM
  3. Replies: 1
    Last Post: 09-03-2013, 07:41 PM
  4. [SOLVED] Copy/Paste multiple columns as values based on another columns criteria
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-28-2013, 06:50 AM
  5. Need one column total if criteria meets another columns criteria
    By jebrown in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-24-2013, 08:47 PM
  6. Replies: 0
    Last Post: 09-15-2012, 02:56 AM
  7. Replies: 2
    Last Post: 10-05-2011, 12:43 PM

Tags for this Thread

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