formula count if
Microsoft Office TrainingThe UK's Number 1 for Microsoft Office Training add this page to your favourites/bookmarksBookmark page

view a printable version of this pagePrintable version
Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formula - Count IF query

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

resolvedResolved · Urgent Priority · Version 2003

No ranking yet
3 posts
replyReplyFri 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!

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

Diamond
2,024 posts
replyReplyFri 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

Diamond
2,024 posts
replyReplyFri 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.


Related articles

· 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


Rate this page:
2.1/5 (107 votes cast)
Institute of IT Training - Accredited Training Provider ILM
Microsoft Certified Partner
Microsoft Office Specialist Authorised Testing Centre (MOS and MCAS)

Prodigy Platinum Learning Partner

Institute of IT Training - Accredited Training Provider
McAfee Secure sites help keep you safe from identity theft, credit card fraud, spyware, spam, viruses and online scams
Association of Computer Trainers verified by visa, mastercard securecard