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
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!