• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Attendance spreadsheet - how to use vlookup to count days

B

big d 51

New Member
Thank you for taking the time to view my post.


File: https://dl.dropbox.com/u/5415677/Attendance.xlsx


Is there any way to use vlookup to calculate the total amount of absent hours for each year for an individual?
 
I'm afraid I can't access your file from my location (CASFFML), but could you not simply use a SUMIF/COUNTIF instead of trying to do a VLOOKUP?
 
@big d 51


The best and the simple way of getting the absent hours is through a pivot table. You just need to have a helper column which would get the "year" from the dates.


or


As said by Luke, you can get those absent hours by using SUMIFS formula. Please refer to the link if incase you need more details


https://www.dropbox.com/s/o7dukefind3nuyn/Copy%20of%20Attendance.xlsx?m


Raja
 
Good day big d 51


Is this what you are looking for?


https://dl.dropbox.com/u/75495784/Attendance.xlsx
 
@Luke M: Thank you for attempting to open. I apologize for the difficultly. SUMIF/COUNTIF is a great idea. Thank you for the input.


@Raja: Thank you for taking the time to work my sample. It made understanding very simple. Thank you.


@bobhc: Thank you for modifying my example as well. Greatly appreciated. The pivot table seems to be headed in the right direction as well, but I need to calculate the total for the year. It should be a simple fix.


Thank you all for the contribution. I am going to tinker some more now that I had some help. You all are really Excel ninjas...I have a ways to go.
 
big d 51


The thing with Pivots is you can do just that. pivot your data. Download the link again.
 
You must log in or register to reply here.
Back
Top