Vlookup + sum in a column

I am creating a template for keeping track of employee's leave. Column "Accumulated Leave" has the vlookup function to return the "leave days" taken by employees. the formula I have is as below:

=VLOOKUP([Employee Name]3, $[Employee Name]8:$Duration50, 5, true)

However, when I show the same employee on an another row, it does not sum up the total days of annual leave requested by the same employee.

Basically, I want to keep record of all leave requested throughout the year and deduct it automatically from the contract annual leave.

Thanks for the help!

Annotation 2019-05-06 181115.png

Find more posts tagged with

Human Resources

Formulas

Comments

Paul Newcome

Try using a SUMIFS instead of a VLOOKUP.

=SUMIFS(range_to_sum, criteria_range_1, criteria_1, criteria_range_2, criteria_2, .............)

christian.boza

I had tried the sumifs formula as well, but i guess I did not do it right at first. Did not research this morning and used the one below:

=SUMIFS(Duration$8:Duration$50, [Employee Name]$8:[Employee Name]$50, "Denise")

and it worked just fine.

Thanks Paul.

Paul Newcome

Happy to help.

A little tip regarding the SUMIF(S)...

They have very different syntax from each other. In SUMIF the range to sum is optional, and if you do want to specify, it comes last.

SUMIFS requires you to specify the range to sum and it comes first.

My recommendation and personal preference is to ALWAYS use the SUMIFS. You can use it with just one set of criteria range/criteria, and it will allow you to add additional criteria later if needed without having to change up the syntax completely.

(I also never use COUNTIF anymore either. Just COUNTIFS)

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…