Sumif based on Vlookup Result

**ferris** · 09-08-2009, 10:29 PM

Hi Guys,

I am trying to achieve the following -

On sheet 2 I have a list of names and positions in column A and B - eg
Col A Col B
Peter Accountant
Paul Engineer
Mark Accountant
John Accountant
Simon Engineer

On sheet 3 I have a list of names (all of which appear in sheet 2) and costs - eg
Col A Col B
Peter $10
Paul $5
Peter $12
Peter $7
Simon $10
Peter $25
Simon $15

On sheet 1 should be a total cost for Engineers and Accountants. Eg the cell next to Engineer in Sheet 1 should have a formula in it that looks at all the names in Column A of Sheet 3, compares each name to Column A Sheet 2, determines if there position in Col B is Engineer then it adds the value in Sheet 3 Column B.

I can achieve this if I put I insert a Column in sheet 3 between the names and the costs, then in this cell I perform a VLOOKUP on the name and it returns a position in the cell. I can then tell sheet 1 to SUMIF the new column in Sheet 3 has Engineer in it.

The thing is I don't want that additional column to be added. The data comes in each month and I don't want anyone to have to add the column I would just like them to copy the Sheet 1 into the workbook and have it all work from there.

Esentially I need something like this on sheet 1
=SUMIF(VLOOKUP('Sheet3'!A2:A10, 'Sheet2'!A2:A10, 2, FALSE), "Engineer", 'Sheet3'!B2:B10)

except this does not work (presumably because you can't enter a range for VLOOKUP).

Many thanks.

**zbor** · 09-09-2009, 03:39 AM

something like this?

Book1.xls

**ferris** · 09-09-2009, 08:45 PM

Thanks for the response but I don't want to have to make any changes to sheet2 or sheet3. I want to know if it is possible to do it all in one step. Your spreadsheet has added up the totals per name and then added up the totals per position. I want to do it all at once so that you don't need the additional column in Sheet2.

**DonkeyOte** · 09-10-2009, 03:44 AM

If zbor's approach isn't viable then assuming sheet2 is sorted based on alphabetical order of Column A (as implied) then if we have results on say sheet1 such that

Please Login or Register  to view this content.

and results to go into B2:B3 accordingly

Please Login or Register  to view this content.

When using SUMPRODUCT it is important to keep ranges to a minimum as the formula is not particularly efficient... to reiterate zbor's previously outlined approach is optimal.

Even if you can't alter sheet2 physically you would, IMO, be better off replicating it via formulae on another sheet and conducting SUMIF as previously outlined.