A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » New Users to Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Formula



 
 
Thread Tools Display Modes
  #1  
Old October 28th 16, 11:08 AM
CharlieBrown76 CharlieBrown76 is offline
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?
Ads
  #2  
Old October 30th 16, 08:51 AM posted to microsoft.public.excel.newusers
Roger Govier[_11_]
external usenet poster
 
Posts: 5
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03: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 10:27 AM.


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