Combining VLOOKUP and SUMIF (or other)

**Lassie4040** · 12-30-2011, 11:28 AM

Hello...

I just joined so please redirect me if I am in the wrong place.

I have 2 files...see attached. There is a lot more information and columns but for these purposes, File 2 shows the same ID number (67890) with a value in different criteria. I'm looking for a way to VLOOKUP in File 2 and combine the value all of the ID numbers that are the same and put them in File 1.

Thank you for any help, suggestions, or referrals...

Lassie

**jeffreybrown** · 12-30-2011, 11:36 AM

By your title it appears you are using a 2002 version of Excel, so...

Try this...

=SUM(IF('[Forum File 2.xls]Sheet1'!$B$8:$B$14=B8,'[Forum File 2.xls]Sheet1'!$C$8:$C$14))

This a CSE formula which means you must enter it with Ctrl + Shift + Enter

**Lassie4040** · 12-30-2011, 12:30 PM

Thank you Jeff!

I think you are on to something here but I am in very unfamiliar territory so if you wouldn't mind, let's try it again with a more realistic set up...see new files attached.

In File 2, you will see in Column B the ID number 41475 showing in 2 separate sections with the amount in Column J as 25. Column O in File 1 should reflect the total as 50. The total for ID number 417113 should show as 25 since it only shows in one section of File 2. Further, ID number 416950 should show as zero (not #N/A please) in File 1 as it is not in File 2 at all.

Please note these are not sheets within one file, but two completely separate excel files.

**jeffreybrown** · 12-30-2011, 02:06 PM

See if this helps. On File 1.xls column O I would get ride of the blanks rows if at all possible so you can just drag the formula down.

Adjust range sizes to meet your real need, but remember, this is a CSE formula so you will need to enter the formula with Ctrl + Shift + Enter