+ Reply to Thread
Results 1 to 14 of 14

Adding multiple values for same lookup

  1. #1
    Registered User
    Join Date
    06-23-2009
    Location
    fairfield, ca
    MS-Off Ver
    Excel 2003
    Posts
    7

    Adding multiple values for same lookup

    It sounds confusing, but I can not figure out how to add two different values with the same lookup criterea.

    EX.

    ...Name.........Number
    1....Mike............34
    2....Joe..............65
    3....Jane.............23
    4....Mike.............13
    5....steve............60

    I want to lookup everytime mike has a number and sum them all together. For this example, I should get 47 as my result.

    I cannot think of a way to do this without a lot of columbs ans repetition. I just want one clean formula. Any thoughts?

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: Adding multiple values for same lookup

    Hi,

    You can use SUMPRODUCT for that one

    =SUMPRODUCT(--(A1:A10="Mike"),--(B1:B10)) alter range to suit yours
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    06-23-2009
    Location
    fairfield, ca
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Adding multiple values for same lookup

    Thank you both.

    OK, lets step it up a notch. What about searching for more than 1 criteria?

    EX.

    .....Color......Name.........Number
    1....Red.......Mike............34
    2....Blue......Joe..............65
    3....Blue......Jane.............23
    4....Green.....Mike.............13
    5....Orange...steve............60
    6....red........Mike............10

    So than I can search for Red and Mike and sum up for a result of 44?

  4. #4
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    1,938

    Re: Adding multiple values for same lookup

    You can use oldchippy's method.

    =SUMPRODUCT(--(A1:A10="Red"),--(B1:B10="Mike"),--(C1:C10))

  5. #5
    Registered User
    Join Date
    06-23-2009
    Location
    fairfield, ca
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Adding multiple values for same lookup

    Still having issues. Below is an example of my data and below that is the Sumproduct that I used. I keep getting zero. I try different parenthises, with and without the "--" ( what is that for ? ) but still no luck...any suggestions?

    40 5/20/2009 1 15*168-19 5,340
    1 5/20/2009 1 15*218-19 9,311
    7 5/20/2009 1 53*218-19 19,075
    40 5/20/2009 2 15*218-19 5,283
    40 5/20/2009 2 27*218-19 240
    40 5/20/2009 2 53*218-19 12,850
    40 5/20/2009 3 27*168-19 6,598
    1 5/20/2009 3 27*218-19 24,994
    40 5/21/2009 1 15*168-19 5,000
    40 5/21/2009 2 27*168-19 0
    7 5/21/2009 3 27*168-19 0

    =SUMPRODUCT(--(A1:A11="40"),--(C1:C11="1"),--(D1-D11="15 168-19"),--(E1:E11))

    I should be getting 10,340 as my result. Instead I get 0.

  6. #6
    Registered User
    Join Date
    05-06-2009
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    85

    Re: Adding multiple values for same lookup

    Your formula is :
    =SUMPRODUCT(--(A1:A11="40"),--(C1:C11="1"),--(D1-D11="15 168-19"),--(E1:E11))

    Yet your data in column D seems to be:
    15*168-19

    There is nothing in Column D that looks like "15 168-19" exactly, which seems like why it would come back as 0 because nothing matches the criteria

    What if you changed your formula to:
    =SUMPRODUCT(--(A1:A11="40"),--(C1:C11="1"),--(D1-D11="15*168-19"),--(E1:E11))

  7. #7
    Registered User
    Join Date
    06-23-2009
    Location
    fairfield, ca
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Adding multiple values for same lookup

    Hey kellyfspringer, thanks for the response. Those are actually spaces in my file, but when I copied and pasted everything into the forum it changed the spaces to asterisks.

    Stormseed - I uploaded a "version" of my file. I deleted/changed any sensitive material, but I tried to leave enough to give you an idea of what I am trying to accomplish. The data on the right will be copied and pasted from another program. I need to take that data and organize it into the table on the left. Please take a look and see if you can see what I am missing here.

    Thanks
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-23-2009
    Location
    fairfield, ca
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Adding multiple values for same lookup

    Thank You Stormseed. Works great and that was a quick response. I am going to really dig into it tomorrow, as this is only a piece of my total goal. Hopefully i won't hit anymore snags, but if I do, I'm sure I'll be right back here. So what was my issue? the format of my formula?

    Thanks again.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Adding multiple values for same lookup

    Quote Originally Posted by Stormseed View Post
    As I told you earlier, SUMPRODUCT() would work with numeric expressions only.
    Not so - although it will obviously only add up numbers, the criteria can be any type (as in the other criteria in that formula)
    The double unary operator helps to coerce the text into a numeric expression of 1 and 0.
    Again, not strictly accurate - the coercion is from TRUE/FALSE to 1/0 respectively. No text involved. (try applying -- to a text value and see what happens. )
    You were trying to write a formula which would look for a specific number as text.
    That was the only issue - looking for "40" rather than 40.

    Just to clarify.
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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