Jo has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Excel Intermediate course
Formula - Count IF query
I am building a spreadsheet that calculates how many working hours are lost per day through sickness. A/L etc. The columns are 1-31 (ie days of the month) and rows are members of staff. One column has each member of staff's normal working hours in.
There are 10 possible reasons for absence, from sickness to Jury Duty, all represented by a letter or symbol. The formula currently is =COUNTIF(G8:G24,"s").
However, whilst this counts the number of each category, it is not working out the total working hours. Unfortunately the team do not work the same amount of hours per day due to variations in contracts, so I cannon simply as a *7.5 to the end. Any suggestions on how I can get it to add the hours up?
Thanks a million!
RE: Formula - Count IF query
Hello Jo
Thank you for your question and welcome to the forum.
Do you have a sample file you can email through to me, even if it is not the one you are working with but a 'miniature' version so I can have a look at this for you? Email to amanda@stl-training.co.uk
Thanks
Amanda
RE: Formula - Count IF query
Hello Jo
Please find attached an amended example of the spreadsheet, which I think solves your problem.
Instead of using a COUNTIF a SUMIF is used in rows 26-35, which takes into account not only the reason why someone was off, but also the number of hours for each individual person.
I put some reasons for sickness in the attached spreadsheet as an example, so you can see that it works (hopefully!). Please have a go at putting some more entries in just to check it works ok.
Kind regards
Amanda
Revised Positive_Return_TEST.xls
