=COUNTIFS multiple criteria numbers, letters and dates

MrJoosten

Board Regular
Joined
Nov 12, 2016
Messages
118
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 nrCustomerStart weekEnd week
5544556Peter510#VALUE!
4455665Mark47
5854545Mark712
5544556Peter69

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

Any help or suggestions are welcome.

Thx :)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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.
 
Upvote 0
=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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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"), "")
9mary666Follow abovepull down
10mary555Follow above
pull down

<tbody>
</tbody>
11john555Follow above
pull down

<tbody>
</tbody>
12jane444Follow above
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>
 
Upvote 0
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"), "")
 
Upvote 0
=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"), "")
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top