Formula - Count IF query

The UK's most regular instructor-led training courses.
Training information: excel consultants · Advanced.excel · Microsoft Excel Training UK
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Urgent Priority · Version 2003

3 posts

ReplyFri 19 Jun 2009, 10:05Delegate Jo said...

Jo has attended:
Access Introduction course
Access Intermediate course
Access Advanced 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!

Version	Date	Location	Places available	Book	Next place rate (£)
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2007	Thu 18 Mar 2010	Bloomsbury	1		£224	£225
2003	Thu 18 Mar 2010	Limehouse	0	FULL
2007	Mon 22 Mar 2010	Limehouse	3		£224	£225
2003	Tue 23 Mar 2010	Southwark	6		£199	£204
2007	Thu 25 Mar 2010	Rochester (Hoo)	0	FULL
2003	Fri 26 Mar 2010	Limehouse	4		£224	£225
Full Schedule: See all 79 Excel Intermediate course dates. Bookings currently available until 23rd December 2010.

2,024 posts

ReplyFri 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

Thanks
Amanda

2,024 posts

ReplyFri 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.xls

Fri 26 Jun 2009: Automatically marked as resolved.

· Excel Advanced Courses Ease the Frustration of Learning Complex Tasks
· Mastering Page Layout In Excel
· What's mine is yours: Sharing data in Excel
· Flexible uses for Excel at Home
· Using Dates Within Excel

Please browse our web site to find out more about
excel consultants and other Microsoft training courses.

Excel tip:

Repeating headings on spreadsheets that print on more than one page

By default when you print a spreadsheet out and it prints on more than one page, the headings at the top and the side of the spreadsheet don't appear on all the pages following page 1.

To get Excel to repeat headings on all pages when printing, go to File - Page Setup - Sheet, then select the rows to repeat at the top of pages, and the columns to repeat at the side of pages by clicking on the red arrows at the right side of the two boxes under the 'Print titles' area. Then click OK.

If you view your spreadsheet in Print Preview, you should see the headings being repeated on each page.

View all Excel hints and tips