Results 1 to 10 of 10

Thread: sum values based off matches from two ranges

  1. #1
    Seeker Bl4ckPantha's Avatar
    Join Date
    May 2018
    Posts
    10
    Articles
    0
    Excel Version
    2016

    sum values based off matches from two ranges



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guru's,

    I have a set of data with multiple ranges which contain matches. Below is an example data set:

    Header 1 Header 2 Header 3 Header 4
    a 1 a 3
    c 3 e 2
    b 1 c 5
    z 4 h 1

    Basically I would like to return the sum value of column header 4 for all matching/duplicated letters under Header 1 and Header 3. In this example there are the letters "a" & "c" under both header 1 and header 2. Therefore, the sum value returned should be 8 (3+5).

    Thank you for your time Guru's.

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    787
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Bl4ckPantha View Post
    return the sum value of column header 4 for all matching/duplicated letters under Header 1 and Header 3.
    This is your eighth post, meaning you know it is desirable to set an example of a workbook.
    Try this formula below and for other example formulas see tutorial 'Sum all values for multiple criteria from one column'.
    Code:
    =SUM(SUMIF(C2:C5;{"A";"C"};D2:D5))
    I hope it helps
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Seeker Bl4ckPantha's Avatar
    Join Date
    May 2018
    Posts
    10
    Articles
    0
    Excel Version
    2016
    Hi Navic,

    Thanks for your reply. Unfortunately this formula does not work. I have also attached a workbook this time.

    The formula needs to be able to sum all matching/duplicate values in column A & C, using column D as the sum range and I'd also like it to ignore any matches if the value in column B is "0" (Zero). The data set I am working with has thousands of unique and matching values between both columns, so I cannot specify which values to sum (e.g. "A" & "C")..

    In the attached I have highlighted the matches based on column B being greater than 0. So all values highlighted in column D should be summed.

    Guru Example.xlsx

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,046
    Articles
    0
    Excel Version
    Office 365 Subscription
    Why not SURF TOWNSVILLE?

    Is this any use?

    =SUMPRODUCT($D$2:$D$9*($C$2:$C$9=$A$2:$A$9))

    Ort this:

    =SUMPRODUCT(IF($B$2:$B$9>0,($C$2:$C$9=$A$2:$A$9)*$D$2:$D$9))

    ... array entered using CTRL+SHIFT+ENTER.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,504
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Quote Originally Posted by Bl4ckPantha View Post
    Hi Navic, Thanks for your reply. Unfortunately this formula does not work.
    Hello,
    please indicate what does not work when you get an answer. It is very frustrating for someone spending his/her free time to see his/her work was not the required solution without knowing what was wrong. All members also try to learn from their mistakes ( if any)

    In this case, did you replace the semi colons in Navic's formula with commas ( see his signature) ?
    Thank you Ken for this secure forum.

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    787
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Bl4ckPantha View Post
    Unfortunately this formula does not work. I have also attached a workbook this time.
    This is something else, much more detailed explanation and expected result with the example Workbook.

    If you've studied my tutorial on link, then you could have come to a solution yourself.
    Look at my example in the attachment.
    I used a formula from tutorial (please see hidden helper columns)
    Code:
    =SUMPRODUCT(D2:D9;--ISNUMBER(MATCH(C2:C9;H2:H9;0)))
    Regards
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7
    Seeker Bl4ckPantha's Avatar
    Join Date
    May 2018
    Posts
    10
    Articles
    0
    Excel Version
    2016
    Hi Navic,

    Thank you so much for your assistance, these formula's provide my exact requirement. I'll be sure to be much clearer next time I post, sorry for wasting your time earlier.

    Regards

  8. #8
    Neophyte ajko7's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    2013

    Sum(if...)

    Hi guys,


    I wrote this formula =SUM(IF(COUNTIF(A2:A9;C2:C9)=1;D29);0)-SUM(IF(COUNTIF(A2:A9;C2:C9)=1;IF(SUMIF(A2:A9;C2:C9;B2:B9)=0;D29;0);0)) that should work correctly too. no helper column is needed.
    you can try it. I added this formula =AND(COUNTIF($A$2:$A$9;$C2)=1;SUMIF($A$2:$A$9;C2;$B$2:$B$9)<>0) to condititional formatting , so you can see, which values the total value consist of.

    Andrej
    Attached Files Attached Files

  9. #9
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    787
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by ajko7 View Post
    I wrote this formula ............... no helper column is needed.
    Great formula
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  10. #10
    Neophyte ajko7's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by navic View Post
    Great formula
    thanks :-).


    I tried it in one formula without the minus part (with if and, or more if), but it din't work. I made something wrong.
    this shortly version should be usefull too: =SUM(IF(COUNTIF(A2:A9;C2:C9)=1;IF(SUMIF(A2:A9;C2:C9;B2:B9)>0;D29;0);0))


    Andrej

Tags for this Thread

Posting Permissions

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