#1   Report Post  
Old October 28th 16, 11:08 AM
Junior Member
 
First recorded activity by ExcelBanter: Oct 2016
Posts: 1
Question Formula

Hi
I am looking at inserting a formula.
I have 2 columns, AM and PM then the days and dates of the week down the left, if someone is off sick then I write it in either AM or PM or both if it is the whole day. I need a formula at the bottom of this that will calculate the total of everything so half days and whole days, can anyone help?

  #2   Report Post  
Old October 30th 16, 09:51 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2016
Posts: 6
Default Formula

On Friday, 28 October 2016 11:49:07 UTC+1, CharlieBrown76 wrote:
Hi
I am looking at inserting a formula.
I have 2 columns, AM and PM then the days and dates of the week down the
left, if someone is off sick then I write it in either AM or PM or both
if it is the whole day. I need a formula at the bottom of this that will
calculate the total of everything so half days and whole days, can
anyone help?




--
CharlieBrown76


Assuming that you have written the word "sick" (without quotes) in the relevant cells, then assuming AM in column C and PM in column D

=COUNTIFS(C:C,"sick") would give the number of mornings off
=COUNTIFS(D,"sick") would give the number of afternoon's off


=COUNTIFS(C:C,"sick",D,"sick")
would give the number of whole days off sick

The advantage of using COUNTIFS over COUNTIF is that you can have multiple parameters, so if you had the persons name in column E you could use
=COUNTIFS(C:C,"sick",D,"sick",E:E,"charlie") to give the number of whole days that Charlie was off sick.

Hope this helps



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Build formula using field values as text in the formula referencing another workbook solardirect Links and Linking in Excel 6 June 4th 12 10:47 PM
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula PJ[_3_] Excel Worksheet Functions 2 June 2nd 10 03:45 PM
Formula expected end of statement error, typing formula into cell as part of VBA macro [email protected] Excel Programming 1 July 20th 06 07:58 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM
Commenting custom formula fields/formula on formula editor Muxer Excel Programming 2 July 24th 03 01:02 AM


All times are GMT +1. The time now is 05:33 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017