98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formula - Count IF query
Formula - Count IF query
Resolved · Urgent Priority · Version 2003
Fri 19 Jun 2009, 10:05Delegate Jo said...
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!
Fri 19 Jun 2009, 10:55Trainer Amanda said...
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
Fri 19 Jun 2009, 12:13Trainer Amanda said...
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
Attached files...
Revised Positive_Return_TEST.xlsFri 26 Jun 2009: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Remove unused toolbar buttonsAre there buttons on your Excel toolbars that you never use? Remove them from the toolbar by doing the following: |
