Tracking Attendance

[SHNOGENS]

I seem to be a bit stuck and any help would be great. I am trying to track 2 items. The first is the number of times a person is sick or late to work. For that it was a simple: =SUM(COUNTIFS(D22:D29,{"SICK","LATE"})). Then I need to count how many different occurrences there were. In my company, if you call out 2 days in a row, that is only considered 1. I can pull the occurrence using: =SUMPRODUCT((C22:C29>=$I$22)*(C22:C29<=$I$23)) ($I$22 and $I$23 being the dates i type in for the past calendar year). Is there a way I am not seeing that I can merge the two together? This is what the sheet will look like. I need to exclude all of the FMLA. So I need 2 rows. One will be total so with the below it would be 4. Then the second column should be 3. Any help would be greatly appreciated.

C22-C29 D22-D29
[TABLE="width: 174"]
<colgroup><col width="87" span="2" style="width:65pt"></colgroup><tbody>[TR]
[TD="class: xl66, align: right"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10/4/18[/TD]
[TD="class: xl65"] FMLA[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9/9/18[/TD]
[TD="class: xl65"] SICK[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9/8/18[/TD]
[TD="class: xl65"] SICK[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8/10/18[/TD]
[TD="class: xl65"] LATE[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"][/TD]
[TD="class: xl65"][/TD]
[/TR]
<!--EndFragment--></tbody><tbody>[TR]
[TD="class: xl63, width: 87, align: right"][/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 87, align: right"][/TD]
[TD="width: 87"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Col Delane]

Welcome to the Forum

I'm not clear as to what outcome you're seeking - 2 results or 3?


If 2, is it:

1. the number of times a person is sick or late to work = 2 (not 3 as two sick days were consecutive)
2. count how many different occurrences there were (including or excluding FMLA?)

If 3, is it:

1. the number of times a person is sick or late to work (not 3 as two sick days were consecutive)
2. count how many different occurrences there were (including FMLA)= 4
3. count how many different occurrences there were (excluding FMLA) = 3

Is there a way I am not seeing that I can merge the two together?

Which two?
If you change your formula to =SUMPRODUCT( (C22:C29>=$I$22) * (C22:C29<=$I$23) * (D22:D29<>"FMLA") ) you'll exclude the FMLA events from the result.