Sumproduct multiple criteria

[Legacy 394724]

	A	B	C	D	E	F	G	H	I
1	Team	ID	Name	SickCalls		Incident	ID	Name	Date
2	Peter	1	Ana	2		SICK	1	Ana	01/02/2017
3						SICK	1	Ana	01/03/2017
4						SICK	1	Ana	01/23/2017

<tbody>
</tbody>

Good evening,

On cell D2 i have the following formula:

=SUMPRODUCT(--(G$2:G$4=$B2),--(F$2:F$4="SICK"),--IF(((G$3:G$5=$B2)*(F$3:F$5="SICK")),((I$2:I$4-I$3:I$5)>1),1))

I'd like the result to be the total number of occurrences someone called in sick.
Consecutive dates count as one single occurrence. Nonconsecutive count as individual occurrences.
At this time the formula only counts if I have other incidents, lets say "Tardy", between my sick calls...otherwise its counting everything as one single occurrence.

Id appreciate any help with this formula.

Best regards,

Ana

 

[Marcelo Branco]

Welcome to Mr Excel

If the names are grouped and the dates for each group are in ascending order, maybe something like this
Dates as mm/dd/yyyy

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Team​	ID​	Name​	SickCalls​		Incident​	ID​	Name​	Date​
2​	Peter​	1​	Ana​	2​		SICK​	1​	Ana​	01/02/2017​
3​		2​	John​	1​		SICK​	1​	Ana​	01/03/2017​
4​		3​	Mary​	3​		SICK​	1​	Ana​	01/23/2017​
5​		4​	Charles​	2​		SICK​	2​	John​	01/04/2017​
6​						SICK​	2​	John​	01/05/2017​
7​						SICK​	2​	John​	01/06/2017​
8​						SICK​	3​	Mary​	01/02/2017​
9​						SICK​	3​	Mary​	01/10/2017​
10​						SICK​	3​	Mary​	01/12/2017​
11​						SICK​	4​	Charles​	01/04/2017​
12​						SICK​	4​	Charles​	01/05/2017​
13​						SICK​	4​	Charles​	01/31/2017​
14​						SICK​	4​	Charles​	02/01/2017​
15​

<tbody>
</tbody>

Formula in D2 copied down
=SUMPRODUCT(--($G$2:$G$100=$B2),--($F$2:$F$100="SICK"),--($I$2:$I$100+1<>($G$3:$G$101=$B2)*$I$3:$I$101))

Hope this helps

M.