=COUNTIFS multiple criteria numbers, letters and dates

[MrJoosten]

Hi guys,

Could anyone help me with this issue;

I want to determine if the dates of an combination of product & customer are overlapping each other.

I think im close, but with the following formula I get an error:

=COUNTIFS(C2:D5;">="&C2;C2:D5;"<="&D2;A2:A5;A2;B2:B5;B2)

This is an simple example sheet where I used the formula (product nr in cel A1):

Product nr	Customer	Start week	End week
5544556	Peter	5	10	#VALUE!
4455665	Mark	4	7
5854545	Mark	7	12
5544556	Peter	6	9

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Any help or suggestions are welcome.

Thx

 

[MrJoosten]

@Aladin Thanks a lot!

=IF(COUNTIFS($A$2:A2,$A2,$B$2:$B2,$B2)>1,IF(COUNTIFS($A$2:$A$5,$A2,$B$2:$B$5,$B2,$C$2:$C$5,"<="&$D2,$D$2:$D$5,">="&$C2)>1,"check discount",""),"")

Works very nice!

 

[Aladin Akyurek]

@Aladin Thanks a lot!

=IF(COUNTIFS($A$2:A2,$A2,$B$2:$B2,$B2)>1,IF(COUNTIFS($A$2:$A$5,$A2,$B$2:$B$5,$B2,$C$2:$C$5,"<="&$D2,$D$2:$D$5,">="&$C2)>1,"check discount",""),"")

Works very nice!

You are welcome.