Sumproduct multiple criteria

[Legacy 394724]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]SickCalls[/TD]
[TD][/TD]
[TD]Incident[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peter[/TD]
[TD]1[/TD]
[TD]Ana[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]SICK[/TD]
[TD]1[/TD]
[TD]Ana[/TD]
[TD]01/02/2017[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SICK[/TD]
[TD]1[/TD]
[TD]Ana[/TD]
[TD]01/03/2017[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SICK[/TD]
[TD]1[/TD]
[TD]Ana[/TD]
[TD]01/23/2017[/TD]
[/TR]
</tbody>[/TABLE]










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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Team​

[/TD]
[TD]

ID​

[/TD]
[TD]

Name​

[/TD]
[TD]

SickCalls​

[/TD]
[TD][/TD]
[TD]

Incident​

[/TD]
[TD]

ID​

[/TD]
[TD]

Name​

[/TD]
[TD]

Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Peter​

[/TD]
[TD]

1​

[/TD]
[TD]

Ana​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

1​

[/TD]
[TD]

Ana​

[/TD]
[TD]

01/02/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

John​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

1​

[/TD]
[TD]

Ana​

[/TD]
[TD]

01/03/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

Mary​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

1​

[/TD]
[TD]

Ana​

[/TD]
[TD]

01/23/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

Charles​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

2​

[/TD]
[TD]

John​

[/TD]
[TD]

01/04/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

2​

[/TD]
[TD]

John​

[/TD]
[TD]

01/05/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

2​

[/TD]
[TD]

John​

[/TD]
[TD]

01/06/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

3​

[/TD]
[TD]

Mary​

[/TD]
[TD]

01/02/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

3​

[/TD]
[TD]

Mary​

[/TD]
[TD]

01/10/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

3​

[/TD]
[TD]

Mary​

[/TD]
[TD]

01/12/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

4​

[/TD]
[TD]

Charles​

[/TD]
[TD]

01/04/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

4​

[/TD]
[TD]

Charles​

[/TD]
[TD]

01/05/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

4​

[/TD]
[TD]

Charles​

[/TD]
[TD]

01/31/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

SICK​

[/TD]
[TD]

4​

[/TD]
[TD]

Charles​

[/TD]
[TD]

02/01/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.