# Adding multiple values for same lookup

1. ## 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. ## 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

3. ## 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. ## Re: Adding multiple values for same lookup

You can use oldchippy's method.

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

5. ## 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. ## Re: Adding multiple values for same lookup

=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. ## 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

8. ## 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. ## Re: Adding multiple values for same lookup

Originally Posted by Stormseed
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.

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

#### 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