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:

	A	B	C
	Code	Quantity	Date
1	102	4	3/31/2018

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

will have the exact data below:

	A	B	C	D	E
1	Code	Quantity	Quantity	Quantity	Date
2	103	2	1	3	3/30/2018
3	101	2	2	2	3/29/2018
4	102	1	2	4	3/30/2018
5	102	1	2	4	3/20/2018

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

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

	A	B	C
	Code	Quantity	Date	RESULT
1	102	4	3/31/2018	TRUE
2	103	3	3/31/2018	TRUE
3	104	1	3/28/2018	FALSE
4	105	2	3/27/2018	FALSE
4	105	2	3/21/2018	TRUE

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

Data 2

	A	B	C	D	E
1	Code	Quantity	Quantity	Quantity	Date
2	103	2	1	3	3/30/2018
3	101	2	2	2	3/29/2018
4	102	1	2	4	3/30/2018
5	105	1	2	4	3/20/2018

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell 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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formulas copied down.

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

 

[jtakw]

Thank you and you're welcome.