=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):
[TABLE="width: 414"]
<tbody>[TR]
[TD]Product nr[/TD]
[TD]Customer[/TD]
[TD]Start week[/TD]
[TD]End week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5544556[/TD]
[TD]Peter[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]4455665[/TD]
[TD]Mark[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5854545[/TD]
[TD]Mark[/TD]
[TD]7[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5544556[/TD]
[TD]Peter[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

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.