+ Reply to Thread
Results 1 to 10 of 10

sum Multiples columns with multiple criteria in a column and criteria in another column

  1. #1
    Registered User
    Join Date
    12-09-2018
    Location
    Montreal
    MS-Off Ver
    2017
    Posts
    28

    sum Multiples columns with multiple criteria in a column and criteria in another column

    Hi,
    Can someone helps me with a formula that would works? I have tried to combine sumproduct, match or sum(sumifs(, but couldn't figure out a good formula.

    Column A Column B Column C Column D
    1) Buy 100 white
    2) Sold 20 white
    3) Give 20 white
    4) Sold 15 Red

    I want to sum B1:C4 for cells of column A that contains "Buy" or "Give" that fits "White" in column D.

    thanks
    kt

  2. #2
    Registered User
    Join Date
    12-09-2018
    Location
    Montreal
    MS-Off Ver
    2017
    Posts
    28

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    Sorry, all the cells were misaligned

    here is screenshot
    excel.jpg

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    Hi,

    This appears to be a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    However try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    12-09-2018
    Location
    Montreal
    MS-Off Ver
    2017
    Posts
    28

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    thanks Richard

    that was much simpler that I was trying to figure out how to put into on formula without thinking to separate them like you did with an addition. That made my need. thanks again

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    My only concern was that your actual data might have lots of columns, necessitating lengthening the formula, hence the caveat.

    However it would be even simpler to put the data in a three column table, (or four if columns B & C mean something different and need to be analysed) and then use a pivot table to avoid any formuale altogether

  6. #6
    Registered User
    Join Date
    12-09-2018
    Location
    Montreal
    MS-Off Ver
    2017
    Posts
    28

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    Richard,

    how to sum B3+C3 when A3="give" AND D3="white"

    (sum two columns with criteria in two columns)

    thanks
    kt

  7. #7
    Registered User
    Join Date
    12-09-2018
    Location
    Montreal
    MS-Off Ver
    2017
    Posts
    28

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    I tried this but it doesn't work

    =SUMPRODUCT((A1:B4="Give")*(D1:D4="White"),B1:C4)

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    Quote Originally Posted by TXT007 View Post
    Richard,

    how to sum B3+C3 when A3="give" AND D3="white"

    (sum two columns with criteria in two columns)

    thanks
    kt
    How is this requirement different to the original and the solution I gave you?

    Please upload the workbook and be sure to include all combinations/permutations of data and manually add the results you expect to see.

  9. #9
    Registered User
    Join Date
    12-09-2018
    Location
    Montreal
    MS-Off Ver
    2017
    Posts
    28

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    example.xlsx

    I could add other sumifs like you told, I have understood that sumproduct needs every array to be the same, that explains why I can't use B1:C4 as array.
    Would like to see if there is shorter formula, as in my real sheet, there are few more columns to sum

    thanks
    kt

  10. #10
    Registered User
    Join Date
    12-09-2018
    Location
    Montreal
    MS-Off Ver
    2017
    Posts
    28

    Re: sum Multiples columns with multiple criteria in a column and criteria in another colum

    Never mind, i found another way to restructure my data sheet

    thanks a lot

+ 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. Sum values in one column based on Criteria in Multiple Columns
    By jcjc7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2017, 02:05 AM
  2. [SOLVED] COUNT across multiple columns with single column criteria
    By AndyGW in forum Excel General
    Replies: 3
    Last Post: 08-13-2017, 08:36 AM
  3. Sum multiple columns, each column based on criteria from different columns
    By windrain in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-19-2017, 08:24 PM
  4. [SOLVED] COUNTIF with multiple criteria on several columns (different column heading)
    By rose4emi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2012, 06:41 AM
  5. Replies: 6
    Last Post: 11-07-2012, 11:18 AM
  6. Replies: 2
    Last Post: 10-05-2011, 12:43 PM
  7. Replies: 3
    Last Post: 09-27-2011, 01:37 PM

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