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, .............)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!