combining vlookup with sum formulas

Nick Amsler

Hello Everyone,

I am stumped again like usual and looking of help with getting a formula right. I am creating a data analysis sheet to generate sums and averages based on another sheet which has raw data. I understand how to do a sumif formula and separately how to write a vlookup formula, but I am struggling to combine the two. My end goal is to have a formula that I can use that sums a certain column if another column matches (the vlookup piece) the column in the analysis sheet. Here is a visual of what I am trying to get.

and this is being gathered from this sheet:

The formula I am typing in every time to get the sums (for just one of them): =SUMIF({ePCR audit data Range 9}, "Barry, Cristian,AMR", {ePCR audit data Range 2})

I am trying to avoid have to write five formulas for each employee and instead have it vlookup from the first column to match the employees name, then do the sums for each data point. I would really like to avoid writing six formulas x 100's of employees and instead have it search for the matching name and sum the data based on the match. I am just looking to somehow combine that vlookup idea with the sum or sumif and can't seem to find a correct syntax or correct order to make it work. I would appreciate any help I can get on this. Thank you in advance!

DKazatsky

Hi Nick,

You should be able to use something like this: =SUMIF({Employee Name}, Name@row, {Sum of Name})

You would need to create one formula per data point but then drag it down for each employee.

{Employee Name} = the column of employee names from the data sheet

Name@row = this is the column in the target sheet that has the employee name being referenced, use your column name here

{Sum of Name} = the range of the data point you want summed from the data sheet **This will change with each data point, name could be anything (Sum of xxx)

Hope this helps,

Dave

DKazatsky

Hi Nick,

You should be able to use something like this: =SUMIF({Employee Name}, Name@row, {Sum of Name})

You would need to create one formula per data point but then drag it down for each employee.

{Employee Name} = the column of employee names from the data sheet

Name@row = this is the column in the target sheet that has the employee name being referenced, use your column name here

{Sum of Name} = the range of the data point you want summed from the data sheet **This will change with each data point, name could be anything (Sum of xxx)

Hope this helps,

Dave

Nick Amsler

@DKazatsky That worked! I didn't think to just use multiple criteria in the sumif/s instead convoluting it with the vlookup.

I really appreciate the help. This just saved me many hours of copy/pasting/editing formulas this weekend. Thanks again!