Formula

[ANGELSDIRTYFACE]

Hi all,

I hope someone can help me with a formula for my table below for the last column Instance No. I want a formula that will number each instance if the department matches and the dates are overlapping or the same. As shown in the table below. Sorry if my explanation isn't brilliant, I'm struggling to find a solution on this.

Event	Start Date	End Date	Department	Instance No
Fire safety	1 Jan 15	3 Jan 15	Health and Safety	1
First Aid	2 Jan 15	3 Jan 15	Health and Safety	2
Cash Handling	1 Jan 15	1 Jan 15	Accounting	1
Travel Claims	2 Jan 15	3 Jan 14	Accounting	1

<tbody>
</tbody>

Many Thanks

Becci

 

[JoeMo]

Copy the formula in E2 down to cover all your data.

Excel Workbook
	A	B	C	D	E
1	Event	Start Date	End Date	Department	Instance No
2	Fire safety	1-Jan-15	3-Jan-15	Health and Safety	1
3	First Aid	2-Jan-15	3-Jan-15	Health and Safety	2
4	Cash Handling	1-Jan-15	1-Jan-15	Accounting	1
5	Travel Claims	2-Jan-15	3-Jan-14	Accounting	2
Sheet5

 

[Marcelo Branco]

Row 5
Is this a typo?

Travel Claims

2 Jan 15

3 Jan 14

Accounting

1

<tbody>
</tbody>

Shouldn't it be?

Travel Claims

2 Jan 15

3 Jan 15

Accounting

1

<tbody>
</tbody>

Please, clarify.

M.

 

[ANGELSDIRTYFACE]

Hi Joe,

Thanks for the quick response, I also need it to countif the dates are overlapping and the dept is the same else the instance would start back at 1, can you help with this too?

Becci

 

[ANGELSDIRTYFACE]

Marcelo, Yes that is a typo sorry!

Regards

Becci

 

[JoeMo]

Hi Joe,

Thanks for the quick response, I also need it to countif the dates are overlapping and the dept is the same else the instance would start back at 1, can you help with this too?

Becci

Sorry, guess I didn't read your post carefully. If the dates are involved then you need to respond to Marcelo's post #3.

 

[Marcelo Branco]

Marcelo, Yes that is a typo sorry!

Regards

Becci

Not sure if i understand what you need.

Maybe...

E2
=COUNTIFS(D$2:D2,D2,B$2:B2,"<="&B2,C$2:C2,">="&C2)
copy down

M.

 

[ANGELSDIRTYFACE]

Hi Marcelo,

thanks I will give that a go and see if it works, essentially if the event overlaps another event and is held by the same dept i want the the number to count.