Countifs Complex

[ryan0521]

Can someone help me to solve this one, I want a formula that will return TRUE or FALSE only if all the following criteria will be met for this DATA.

I want to check if all the data here below:
[TABLE="width: 310"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Code[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]102[/TD]
[TD]4[/TD]
[TD]3/31/2018[/TD]
[/TR]
</tbody>[/TABLE]

will have the exact data below:
[TABLE="width: 488"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Code[/TD]
[TD]Quantity[/TD]
[TD]Quantity[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]103[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3/30/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]101[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3/29/2018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]102[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3/30/2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]102[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3/20/2018
[/TD]
[/TR]
</tbody>[/TABLE]

But I need to have additional 1 day allowance for date, so exact date or late by 1 day is alright and the quantity of 1st data can be match with any of 3 Quantity from the 2nd table.

Hope you help me.

 

[jtakw]

Hi,

Not sure if you mean you want to check your second table line by line, or as a whole, as you didn't show sample of results needed, is this what you mean?

Excel 2010
	A	B	C	D	E	F
1	Code	Quantity	Date
2	102	4	3/31/2018
3
4
5	Code	Quantity	Quantity	Quantity	Date
6	103	2	1	3	3/30/2018	FALSE
7	101	2	2	2	3/29/2018	FALSE
8	102	1	2	4	3/30/2018	TRUE
9	102	1	2	4	3/20/2018	FALSE
Sheet9

Cell Formulas
Range	Formula
F6	=AND(A6=A$2,COUNTIF(B6:D6,B$2),OR(E6=C$2,E6=C$2-1))

F6 formula copied down.

 

[ryan0521]

This one is good. Thank you so much.

 

[jtakw]

You're welcome.

 

[ryan0521]

You're welcome.

What if, I have many data in table 1, how can I check if the in the first row of data in table 1 have the same data on data given with the same rulings.

 

[jtakw]

Can you show some samples of what you have, and sample results.

 

[ryan0521]

Can you show some samples of what you have, and sample results.

Data 1

[TABLE="width: 384"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Code[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]102[/TD]
[TD]4[/TD]
[TD]3/31/2018[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]103[/TD]
[TD]3[/TD]
[TD]3/31/2018[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]104[/TD]
[TD]1[/TD]
[TD]3/28/2018[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]105[/TD]
[TD]2[/TD]
[TD]3/27/2018[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]105[/TD]
[TD]2[/TD]
[TD]3/21/2018[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

Data 2
[TABLE="width: 496"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Code[/TD]
[TD]Quantity[/TD]
[TD]Quantity[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]103[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3/30/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]101[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3/29/2018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]102[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3/30/2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]105[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3/20/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

This seems to work:

Excel 2010
	A	B	C	D	E	F
1	Code	Quantity	Date
2	102	4	3/31/2018	TRUE
3	103	3	3/31/2018	TRUE
4	104	1	3/28/2018	FALSE
5	105	2	3/27/2018	FALSE
6	105	2	3/21/2018	TRUE
7
8
9	Code	Quantity	Quantity	Quantity	Date
10	103	2	1	3	3/30/2018	FALSE
11	101	2	2	2	3/29/2018	FALSE
12	102	1	2	4	3/30/2018	TRUE
13	105	1	2	4	3/20/2018	FALSE
Sheet21

Cell Formulas
Range	Formula
D2	=SUMPRODUCT((A$10:A$13=A2)*(B$10:D$13=B2)*OR(E$10:E$13=C2,E$10:E$13=C2-1))>0
F10	=AND(A10=A$2,COUNTIF(B10:D10,B$2),OR(E10=C$2,E10=C$2-1))

Formulas copied down.

 

[ryan0521]

This seems to work:

Excel 2010

	A	B	C	D	E	F
Code	Quantity	Date
Code	Quantity	Quantity	Quantity	Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3/28/2018[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3/27/2018[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3/21/2018[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3/30/2018[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3/29/2018[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3/30/2018[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3/20/2018[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUMPRODUCT((A$10:A$13=A2)*(B$10:D$13=B2)*OR(E$10:E$13=C2,E$10:E$13=C2-1))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F10[/TH]
[TD="align: left"]=AND(A10=A$2,COUNTIF(B10:D10,B$2),OR(E10=C$2,E10=C$2-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formulas copied down.

Thank you so much, you help me a lot. God Bless you.

 

[jtakw]

Thank you and you're welcome.