Sumif?

Hi,

In the first worksheet I have a set of codes which I need to compare to another worksheet and find matching codes and return the value associated with it in the adjacent column. Normally I would use a vlookup however in this second worksheet I have 4 columns set out as below:

Code | Number | Code | Number

x123 2 z179 3
y567 1 x123 2

I would therefore need to look in both "code" columns to see if I can find the code from the first worksheet and take the number from the "number" column to its right. One additional problem, the code we are looking for could appear in BOTH columns and in which case I would need to sum both values. Therefore from the above example I would get:

x123 = 4
z179 = 3
y567 = 1

Is this possible in either a formula or macro? I would refer the former but please let me know about either.

Many thanks in advance,

GW

Ah, as simple as that! I think I imagined something much more complicated in my mind. Thanks, no sample file needed, I'm fine with sumif's, just never occurred to me to add the two together!

Thanks again,

GW

Originally Posted by Excel_Geek

You'd add two SUMIFs:

=SUMIF($A$2:$A$3,$A7,$B$2:$B$3)+SUMIF($C$2:$C$3,$A7,$D$2:$D$3)

A2:A3 is the left column of codes, B2:B3 is the left column of numbers.

C2:C3 is the right column of codes, D2:D3 is the right column of numbers.

A7 is the code you're trying to sum up.

Let me know if you'd like a sample file.