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!
Comments
-
Try using a SUMIFS instead of a VLOOKUP.
Β
=SUMIFS(range_to_sum, criteria_range_1, criteria_1, criteria_range_2, criteria_2, .............)
-
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.
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!