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?

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:D,"sick") would give the number of afternoon's off


=COUNTIFS(C:C,"sick",D: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:D,"sick",E:E,"charlie") to give the number of whole days that Charlie was off sick.

Hope this helps