# =COUNTIFS multiple criteria numbers, letters and dates

#### MrJoosten

##### Board Regular
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### sweiming91

##### New Member
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

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

#### sweiming91

##### New Member
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.

##### Well-known Member
=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))<>

Last edited:

#### MrJoosten

##### Board Regular
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.

##### MrExcel MVP
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

##### New Member
Maybe you can try this..
IJKLMN
8john555=I8&"-"&J8start week eg 1end week eg 5=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"), "")
 pull down

<tbody>
</tbody>
 pull down

<tbody>
</tbody>
 pull down

<tbody>
</tbody>

<tbody>
</tbody>

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;">
 pull down

<tbody>
</tbody>
</body>

#### sweiming91

##### New Member
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

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

Replies
0
Views
143
Replies
2
Views
99
Replies
1
Views
331
Replies
7
Views
227
Replies
0
Views
190

1,191,080
Messages
5,984,510
Members
439,894
Latest member
Amba1006

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back