Remember Me?

#1
October 28th 16, 11:08 AM
 Junior Member First recorded activity by ExcelBanter: Oct 2016 Posts: 1
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
October 30th 16, 08:51 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Oct 2016 Posts: 5
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post solardirect Links and Linking in Excel 6 June 4th 12 10:47 PM PJ[_3_] Excel Worksheet Functions 2 June 2nd 10 03:45 PM [email protected] Excel Programming 1 July 20th 06 07:58 PM o0o0o0o Excel Worksheet Functions 6 November 19th 04 03:13 PM Muxer Excel Programming 2 July 24th 03 01:02 AM

All times are GMT +1. The time now is 09:54 PM.