# Sum values that correspond to

1. ## Sum values that correspond to

Hi there,

I've been having some trouble with the following..

I have something like:
columns A B (sheet1)
u 2
v 1
w 5
x 3
y 1
z 2

and in a seperate sheet:

column D (sheet2)
w
z
u

I would like to get a sum of the values in sheet1!B:B that correspond to the values in sheet2!D:D (where only those in sheet1!A:A that match sheet2!D:D are sumed). In the above example I would like to get = 9.

I had read that using something like the following would work but I just get the "#VALUE!" error...
Formula:
`Please Login or Register  to view this content.`

Does it matter that my actual sheet1!A:A and sheet2!D:D look more like "xyz, abc"?

Geoff.

2. ## Re: Sum values that correspond to

enter this into E2 and fill down

``Please Login or Register  to view this content.``

3. ## Re: Sum values that correspond to

Hi Mehmetcik,

Thanks for your reply. I tried it but it's not quite what I'm looking for.

I just need a formula in a single cell that sums the values in sheet1!B:B for which sheet1!A:A and sheet2!D:D are the same. In other words, I would like enter a formula in cell E2 (for example) that would result in the answer "9".

I am making sense?

Also I apologise for the title of this post (it was left incomplete accidentally)! I would have liked it to say "SUMIF + SUMPRODUCT? - Sum of B:B when A:A = D:D".

Geoff.

4. ## Re: Sum values that correspond to

I don't know why you're getting a #VALUE error. I copied your formula and it worked fine.
/shrug

-Z

5. ## Re: Sum values that correspond to

Maybe this

in E1 and copy down

=SUMPRODUCT((\$A\$1:\$A\$6=D1)*(\$B\$1:\$B\$6))

 A B C D E 1 u 2 w 5 2 v 1 z 2 3 w 5 u 2 4 x 3 5 y 1 6 z 2

or for Total

=SUMPRODUCT((\$A\$1:\$A\$6={"w","z","u"})*(\$B\$1:\$B\$6))

Or

=SUM(SUMIF(A1:A6,D1,B1:B6),SUMIF(A1:A6,D2,B1:B6),SUMIF(A1:A6,D3,B1:B6))

6. ## Re: Sum values that correspond to

Don't use the entire column D as the criteria reference. Use a smaller specific range like this:

=SUMPRODUCT(SUMIF(Sheet1!A:A,Sheet2!D2:D5,Sheet1!B:B))

7. ## Re: Sum values that correspond to

Thanks Zodeeak, Alkey and Tony Valko (again .

I tried it again as Tony Valko suggested and sure enough, it worked fine. I don't know why I didn't think of that!
Zodeeak, did it work for you as I had done it or as Tony Valko suggested?

Cheers everyone,

Geoff.

8. ## Re: Sum values that correspond to

You're welcome. Thanks for the feedback!

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