=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

 

[sweiming91]

Hi..

Maybe you can use things like create a new column in front of the product column, then =product column&customer column.. such as =C2+D2, then you sort the entire table

 

[Aladin Akyurek]

Would you express what you intend to accomplish in words instead of a formula?

 

[sweiming91]

Yes.

What you would do is to combine product and customer together in a single cell. Then when you sort the entire table according to the combined value, u will get them to be grouped together. Next you would like to use pivot table to get the overlapping values you want such as maximum and minimum.

 

[admiral100]

=SUMPRODUCT(--($C$2:$D$5 > MIN(C2)),--($C$2:$D$5 < MAX(D2)),--($A$2:$B$5=A2:B2))<max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))< html=""></max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))<max(d2)),--($a$2:$b$5=a2:b2))<>

 

[MrJoosten]

Thanks for your thinking with me guys,

I'm not quite there yet.

In words:
I'm setting up a discount system.

A product and customer may not overlap in dates. So when customer Peter gets discount in week 5-10 on product 5544556, I want to check if Peter is already receiving discount between that dates on the same product.

A formula would be nice, but other methods are welcome too.

 

[Aladin Akyurek]

In E2 enter and copy down:

=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",""),"")

Does this fit the requirement?

 

[sweiming91]

Maybe you can try this..
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]john[/TD]
[TD]555[/TD]
[TD]=I8&"-"&J8[/TD]
[TD]start week eg 1[/TD]
[TD]end week eg 5[/TD]
[TD]=IFERROR(IF(IF(K8=K9, OR(L8=L9,M8=M9,M8=L9,L8=M9,AND(M8>L9,M8<M9),AND(M9>L8,M9<M8)), ""), "Overlapped", "Not Overlapped"), "")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]mary[/TD]
[TD]666[/TD]
[TD]Follow above[/TD]
[TD][/TD]
[TD][/TD]
[TD]pull down
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]mary[/TD]
[TD]555[/TD]
[TD]Follow above[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]pull down[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]john[/TD]
[TD]555[/TD]
[TD]Follow above[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]pull down[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]jane[/TD]
[TD]444[/TD]
[TD]Follow above[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]pull down[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Do sorting, First level to be column K, second level column L.
If you see that there is a overlap, means the row and the row after overlapped. ****** id="cke_pastebin" style="position: absolute; top: 141px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]pull down[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[sweiming91]

Sorry

N8 is =IFERROR(IF(IF(K8=K9, OR(L8=L9,M8=M9,M8=L9,L8=M9,AND(M8>L9,M8<M9),AND(M9>L8,M9<M8)), ""), "Overlapped", "Not Overlapped"), "")

 

[sweiming91]

=IFERROR(IF(IF(K8=K9, OR(L8=L9,M8=M9,M8=L9,L8=M9,AND(M8>L9,M8<M9),AND(M9 > L8,M9<M8)), ""), "Overlapped", "Not Overlapped"), "")