How to get a specific result using IF formula based on multiple conditions?

[rizwanulhasan]

Hi,

Hope all are well.

My requirements are as follows:

C53 should show as DRF COMPLETE if the below conditions are fulfilled.

1. C5 to C10, C14, C32, C40, C43, C44, C46, C47 should not be blank

2. C8 should always be a number of 14 digits

3. Only if C6=USA, C11 should not be blank.

4. Only if C14="Partial Batch Decommissioning",

i> C20 should not be blank and C55 should be "QUANTITY MATCH"

ii> If C20 = SGTIN Level - B28 should not be blank and should contain a numerical value. Similarly for below:

Case Level - B28 & C28
Pallet Level - B28 & D28
Cases + Pallets - C28 & D28
SGTIN + Cases - B28 & C28
SGTIN + Pallets - B28 & D28
SGTIN + Cases + Pallets - B28, C28 & D28

I am using below formula. Everything's perfect, just that 1 condition doesn't work. When C14=Partial Batch Decommissioning, despite C55=QUANTITY MISMATCH, C53 shows DRF COMPLETE whereas it should show DRF INCOMPLETE.

="DRF"&IF(AND(AND(COUNTA(C5:C10)=6,C14<>"",C32<>"",C40<>"",COUNTA(C43,C44,C46,C47)=4),AND(LEN(C8)=14,ISNUMBER(C8+0),IF(C6="USA",C11<>"",TRUE),IF(C14="Partial Batch Decommissioning",AND(C20<>"",TRUE),C55="QUANTITY MATCH"),AND(OR(AND(C20="SGTIN Level",B28<>""),AND(C20="Case Level",COUNT(B28:C28)=2),AND(C20="Pallet Level",COUNT(B28,D28)=2),AND(C20="SGTIN + Cases",COUNT(B28,C28)=2),AND(C20="Cases + Pallets",COUNT(C28,D28)=2),AND(C20="SGTIN + Pallets",COUNT(B28,D28)=2),AND(C20="SGTIN + Cases + Pallets",COUNT(B28:D28)=3))))), " COMPLETE"," INCOMPLETE")

Note: This is a cross post and the link to original post is as below.

How to get a specific result using IF formula based on multiple conditions?

 

[SanjayGMusafir]

Check this and revert -

="DRF"&IFS(AND(COUNTIFS(C5:C10,"<>")=0,C14<>"",C32<>"",C40<>"",C43<>"",C44<>"",C46<>"",C47<>"",ISNUMBER(C8),LEN(C8)=14,AND(C6="USA",C11=""),AND(C14="Partial Batch Decommissioning",OR(AND(C20<>"",C55="QUANTITY MATCH"),AND(C20="SGTIN Level",B28<>""),AND(C20="Case Level",B28<>"",C28<>""),AND(C20="Pallet Level",B28<>"",D28<>""),AND(C20="Cases + Pallets",C28<>"",D28<>""),AND(C20="SGTIN + Cases",B28<>"",C28<>""),AND(C20="SGTIN + Pallets",B28<>"",D28<>""),AND(C20="SGTIN + Cases + Pallets",B28<>"",C28<>"",D28<>""))))," COMPLETE",TRUE," INCOMPLETE")

 

[SanjayGMusafir]

I think it should be modified as below -

="DRF"&IFS(AND(COUNTIFS(C5:C10,"<>")=6,C14<>"",C32<>"",C40<>"",C43<>"",C44<>"",C46<>"",C47<>"",ISNUMBER(C8),LEN(C8)=14,AND(C6="USA",C11=""),AND(C14="Partial Batch Decommissioning",OR(AND(C20<>"",C55="QUANTITY MATCH"),AND(C20="SGTIN Level",B28<>""),AND(C20="Case Level",B28<>"",C28<>""),AND(C20="Pallet Level",B28<>"",D28<>""),AND(C20="Cases + Pallets",C28<>"",D28<>""),AND(C20="SGTIN + Cases",B28<>"",C28<>""),AND(C20="SGTIN + Pallets",B28<>"",D28<>""),AND(C20="SGTIN + Cases + Pallets",B28<>"",C28<>"",D28<>""))))," COMPLETE",TRUE," INCOMPLETE")

 

[rizwanulhasan]

Hi Sanjay,

Many thanks for your response.

I tried both formulae but they doesn't work. Was just wondering how to upload workbook to the post.

 

[SanjayGMusafir]

Hi Sanjay,

Many thanks for your response.

I tried both formulae but they doesn't work. Was just wondering how to upload workbook to the post.

You can't upload workbook here in this forum

You can upload XL2BB here that is quite helpful.

 

[SanjayGMusafir]

I tried both formulae but they doesn't work

Just now I tested it for all conditions one by one. It is working.

Check this and revert -

Book2
	A	B	C	D
1	DRF COMPLETE
2	DRF COMPLETE
3
4
5			A
6			USA
7			A
8			1.23457E+13
9			A
10			A
11
12
13
14			Partial Batch Decommissioning
15
16
17
18
19
20			SGTIN + Cases
21
22
23
24
25
26
27
28		A	A	A
29
30
31
32			A
33
34
35
36
37
38
39
40			A
41
42
43			A
44			A
45
46			A
47			A
48
49
50
51
52
53
54
55
Sheet1

Cell Formulas
Range	Formula
A1	A1	="DRF"&IFS(AND(COUNTIFS(C5:C10,"<>")=6,C14<>"",C32<>"",C40<>"",C43<>"",C44<>"",C46<>"",C47<>"",ISNUMBER(C8),LEN(C8)=14,AND(C6="USA",C11=""),AND(C14="Partial Batch Decommissioning",OR(AND(C20<>"",C55="QUANTITY MATCH"),AND(C20="SGTIN Level",B28<>""),AND(C20="Case Level",B28<>"",C28<>""),AND(C20="Pallet Level",B28<>"",D28<>""),AND(C20="Cases + Pallets",C28<>"",D28<>""),AND(C20="SGTIN + Cases",B28<>"",C28<>""),AND(C20="SGTIN + Pallets",B28<>"",D28<>""),AND(C20="SGTIN + Cases + Pallets",B28<>"",C28<>"",D28<>""))))," COMPLETE",TRUE," INCOMPLETE")
A2	A2	="DRF"&IFS(AND(TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,AND(C14="Partial Batch Decommissioning",OR(AND(C20<>"",C55="QUANTITY MATCH"),AND(C20="SGTIN Level",B28<>""),AND(C20="Case Level",B28<>"",C28<>""),AND(C20="Pallet Level",B28<>"",D28<>""),AND(C20="Cases + Pallets",C28<>"",D28<>""),AND(C20="SGTIN + Cases",B28<>"",C28<>""),AND(C20="SGTIN + Pallets",B28<>"",D28<>""),AND(C20="SGTIN + Cases + Pallets",B28<>"",C28<>"",D28<>""))))," COMPLETE",TRUE," INCOMPLETE")

 

[SanjayGMusafir]

3. Only if C6=USA, C11 should not be blank.

For this condition I have made a minor modification in the formula. Check this and revert -

="DRF"&IFS(AND(COUNTIFS(C5:C10,"<>")=6,C14<>"",C32<>"",C40<>"",C43<>"",C44<>"",C46<>"",C47<>"",ISNUMBER(C8),LEN(C8)=14,AND(C6="USA",C11=""),AND(C14="Partial Batch Decommissioning",OR(AND(C20<>"",C55="QUANTITY MATCH"),AND(C20="SGTIN Level",B28<>""),AND(C20="Case Level",B28<>"",C28<>""),AND(C20="Pallet Level",B28<>"",D28<>""),AND(C20="Cases + Pallets",C28<>"",D28<>""),AND(C20="SGTIN + Cases",B28<>"",C28<>""),AND(C20="SGTIN + Pallets",B28<>"",D28<>""),AND(C20="SGTIN + Cases + Pallets",B28<>"",C28<>"",D28<>""))))," COMPLETE",TRUE," INCOMPLETE")

There are so many conditions to test some confusion or error can remain, that only you can test or verify as per your needs.

 

[rizwanulhasan]

Unfortunately, not working. Sheet contains data validations because of which i am unable to upload XL2BB as well.

 

[rizwanulhasan]

Form-1.xlsx
	A	B	C	D
1
2
3
4	To Be Completed by Requestor	Decommissioning Product	Enter Product Details
5		Supply Site / Market Warehouse / LOC	a
6		GTIN Market	USA
7		Batch / Lot Number	a
8		GTIN (Global Trade Item Number)	05054290000931
9		Trade Item Description	a
10		SKU / Material Number	a
11		Case GTIN (for US Market Only)	DRF INCOMPLETE
12
13		Types of Decommissioning	Choose Type of Decommissioning
14		Full Batch Decommissioning with Rework	Partial Batch Decommissioning
15		Full Batch Decommissioning without Rework
16		Partial Batch Decommissioning
17
18			Scroll down for more inputs
19		Levels of Decommissioning	Choose Level of Decommissioning
20		SGTIN Level	SGTIN Level
21		Case Level
22		Pallet Level
23		Cases + Pallets
24		SGTIN + Cases
25		SGTIN + Pallets
26		SGTIN + Cases + Pallets
27		Enter SGTIN Quantity	Enter Case Quantity	Enter Pallet Quantity
28		2	1	1
29		Note	Enter SGTIN quantity. Also ensure that the same count of SGTINs are listed at the end of this form.
30
31		Decommissioning Reason Codes	Choose Reason Code for Decommissioning
32		FREE SAMPLE	SAMPLE
33		SAMPLE
34		DESTROYED
35		SUPPLIED
36		EXPORTED
37		OTHERS
38		LOCKED
39		RECALL
40		Decommissioning Comments (if any)	N/A
41
42		Data Provider	Enter Details	Description of Role
43		MUDID	a
44		Name	a
45		Quality Approver	Enter Details	Description of Role
46		MUDID	a
47		Name	a
48
49		SUMMARY	Partial Batch Decommissioning for batch a associated with GTIN 05054290000931 using reason code SAMPLE
50
51
52
53		DRF Completion Status (For details, refer row 20 in Instructions sheet)	DRF INCOMPLETE
54
55	To Be Completed by Requestor	Quantity Verification: (For details, refer row 23 in Instructions sheet)	QUANTITY MIS-MATCH
56		Quantity of Serial Number/SGTINs Entered	Quantity of Cases Entered	Quantity of Pallets Entered
57		1	1	1
58		Enter Serial numbers / SGTINs to be decommissioned	Enter Case numbers (SGTINs / SSCCs) to be decommissioned	Enter Pallet numbers (SSCCs) to be decommissioned
59		00000000000001	00000000000001	00000000000001
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
Decommissioning Request Form

Cell Formulas
Range	Formula
C29	C29	=IF(C20="","",IF(C20="SGTIN Level","Enter SGTIN quantity. Also ensure that the same count of SGTINs are listed at the end of this form.",IF(C20="Case Level","Enter total SGTIN quantity of the case and Case quantity. Also ensure that the same count of case numbers are listed at the end of this form.",IF(C20="Pallet Level","Enter total SGTIN quantity of the pallet and Pallet quantity. Also ensure that the same count of pallet numbers are listed at the end of this form.",IF(C20="Cases + Pallets","Enter total number of Cases in the pallet and Pallet quantity. Also ensure that the same count of cases and pallet numbers are listed at the end of this form.",IF(C20="SGTIN + Cases","Enter both SGTIN and Case quantity. Also ensure that the same count of SGTINs and case numbers are listed at the end of this form.",IF(C20="SGTIN + Pallets","Enter both SGTIN and Pallet quantity. Also ensure that the same count of SGTINs and pallet numbers are listed at the end of this form.",IF(C20="SGTIN + Cases + Pallets","Enter SGTINs, Cases and Pallets quantity. Also ensure that the same count of SGTINs, Cases and Pallet numbers are listed at the end of this form."))))))))
C49	C49	=" "&$C$14&" for batch "&$C$7&" associated with GTIN "&$C$8&" using reason code "&$C$32
C53	C53	="DRF"&IFS(AND(COUNTIFS(C5:C10,"<>")=6,C14<>"",C32<>"",C40<>"",C43<>"",C44<>"",C46<>"",C47<>"",ISNUMBER(C8),LEN(C8)=14,IF(C6="USA",C11<>"",TRUE),AND(C14="Partial Batch Decommissioning",OR(AND(C20<>"",C55="QUANTITY MATCH"),AND(C20="SGTIN Level",B28<>""),AND(C20="Case Level",B28<>"",C28<>""),AND(C20="Pallet Level",B28<>"",D28<>""),AND(C20="Cases + Pallets",C28<>"",D28<>""),AND(C20="SGTIN + Cases",B28<>"",C28<>""),AND(C20="SGTIN + Pallets",B28<>"",D28<>""),AND(C20="SGTIN + Cases + Pallets",B28<>"",C28<>"",D28<>""))))," COMPLETE",TRUE," INCOMPLETE")
C55	C55	=IF(C20="SGTIN Level",IF(B28="","ENTER SGTIN QUANTITY",IF(B28=B57,"QUANTITY MATCH","QUANTITY MIS-MATCH")),IF(C20="Case Level",IF(COUNT(B28,C28)<>2,"ENTER SGTIN & CASE QUANTITY",IF(C28=C57,"QUANTITY MATCH","QUANTITY MIS-MATCH")),IF(C20="Pallet Level",IF(COUNT(B28,D28)<>2,"ENTER SGTIN & PALLET QUANTITY",IF(D28=D57,"QUANTITY MATCH","QUANTITY MIS-MATCH")),IF(C20="Cases + Pallets",IF(COUNT(C28,D28)<>2,"ENTER CASE & PALLET QUANTITY",IF(AND(C28=C57,D28=D57),"QUANTITY MATCH","QUANTITY MIS-MATCH")),IF(C20="SGTIN + Cases",IF(COUNT(B28,C28)<>2,"ENTER SGTIN & CASE QUANTITY",IF(AND(B28=B57,C28=C57),"QUANTITY MATCH","QUANTITY MIS-MATCH")),IF(C20="SGTIN + Pallets",IF(COUNT(B28,D28)<>2,"ENTER SGTIN & PALLET QUANTITY",IF(AND(B28=B57,D28=D57),"QUANTITY MATCH","QUANTITY MIS-MATCH")),IF(C20="SGTIN + Cases + Pallets",IF(COUNT(B28,C28,D28)<>3,"ENTER SGTIN, CASE & PALLET QUANTITY",IF(AND(B28=B57,C28=C57,D28=D57),"QUANTITY MATCH","QUANTITY MIS-MATCH")))))))))
B57:D57	B57	=COUNTIF(B59:B1048576,"<>")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B19:D29	Expression	=$C$14<>"Partial Batch Decommissioning"	text	YES
A55:D95	Expression	=$C$14<>"Partial Batch Decommissioning"	text	NO
B27:D29	Expression	=$C$20=""	text	NO
C27:D27	Expression	=$C$20="SGTIN Level"	text	NO
C27	Expression	=$C$20="Pallet Level"	text	NO
A55:D95,A55:C55	Expression	=$C$20=""	text	NO
C18	Expression	=C14="Partial Batch Decommissioning"	text	NO
C28	Expression	=$C$20="Pallet Level"	text	NO
C28:D28	Expression	=$C$20="SGTIN Level"	text	NO
B56:B95	Expression	=(OR($C$20="Case Level"))	text	NO
C55	Expression	=OR(C55="ENTER SGTIN QUANTITY",C55="ENTER SGTIN & CASE QUANTITY",C55="ENTER SGTIN & PALLET QUANTITY",C55="ENTER CASE & PALLET QUANTITY",C55="ENTER SGTIN & CASE QUANTITY",C55="ENTER SGTIN & PALLET QUANTITY",C55="ENTER SGTIN, CASE & PALLET QUANTITY")	text	NO
B56:B95	Expression	=(OR($C$20="Pallet Level"))	text	NO
D27	Expression	=(OR($C$20="Case Level",$C$20="SGTIN + Cases"))	text	NO
B11:D11	Expression	=$C$6<>"USA"	text	NO
C53	Expression	=C53="DRF INCOMPLETE"	text	NO
C53	Expression	=C53="DRF COMPLETE"	text	NO
C55	Expression	=C55="QUANTITY MATCH"	text	NO
C55	Expression	=C55="QUANTITY MIS-MATCH"	text	NO
C27	Expression	=$C$20="SGTIN + Pallets"	text	NO
C28	Expression	=$C$20="SGTIN + Pallets"	text	NO
D28	Expression	=(OR($C$20="Case Level",$C$20="SGTIN + Cases"))	text	NO
B27,B56:B95	Expression	=(OR($C$20="Cases + Pallets"))	text	NO
B28	Expression	=(OR($C$20="Cases + Pallets"))	text	NO
C56:C95	Expression	=$C$20="SGTIN Level"	text	NO
C56:C95	Expression	=$C$20="Pallet Level"	text	NO
C56:C95	Expression	=$C$20="SGTIN + Pallets"	text	NO
D56:D95	Expression	=(OR($C$20="SGTIN Level",$C$20="Case Level",$C$20="SGTIN + Cases"))	text	NO
C49	Expression	=C47=""	text	NO

 

[SanjayGMusafir]

Unfortunately, not working

Test for all rules and tell me which particular condition is not working... Then only I can look into formula and check for errors. I personally checked all conditions and it was working.