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]

In the sample data you just gave C8 is 13 digit number + starting with 0 thus making it look 14 digits

This shall always give error when testing for 14 digits and thus one condition will always mismatch.

Book3
	C	D	E
8	05054290000931	13	TRUE
Sheet1

Cell Formulas
Range	Formula
D8	D8	=LEN(C8)
E8	E8	=ISNUMBER(C8)

 

[rizwanulhasan]

Its always showing DRF INCOMPLETE. So taking long to test. Meanwhile, is below correct for C5 to C10 should not be blank. Sorry i am naïve at excel. So though to ask.

(COUNTIFS(C5:C10,"<>")=6

 

[rizwanulhasan]

Strangely, its 14 for me

 

[SanjayGMusafir]

Strangely, its 14 for me

You need to understand if it passes the test for 14 digits, it will fail the test for number. So you have to drop one of the conditions from testing

Book3
	H	I	J
9	05054290000931	14	FALSE
10	05054290000931	13	TRUE
Sheet1

Cell Formulas
Range	Formula
I9:I10	I9	=LEN(H9)
J9:J10	J9	=ISNUMBER(H9)

 

[SanjayGMusafir]

Try this and don't change any part of it. You are modifying formula for no reason -

="DRF"&IFS(AND(COUNTIFS(C5:C10,"<>")=6,C14<>"",C32<>"",C40<>"",C43<>"",C44<>"",C46<>"",C47<>"",LEN(C8)=14,OR(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")

Book3
	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	14
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 COMPLETE
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		1	1	1
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
Sheet1

Cell Formulas
Range	Formula
D8	D8	=LEN(C8)
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<>"",LEN(C8)=14,OR(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")
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,"<>")

 

[rizwanulhasan]

Hi Sanjay, tried latest formula. Below are the issues.

1. If C6 is anything apart from USA, C53 shows DRF INCOMPLETE where as it should show DRF COMPLETE

2. If C6 = "USA" and C11 = blank, C53 shows DRF COMPLETE whereas it should show DRF INCOMPLETE

The actual requirement is that if C6="USA", then C11 should not be blank

3. When C14 = "Partial Batch Decommissioning" and C20 = blank, C53 shows DRF COMPLETE whereas it should show DRF INCOMPLETE

The actual requirement is that if C14 = "Partial Batch Decommissioning", then C20 should not be blank

4. When C14 = "Partial Batch Decommissioning" and C55= "QUANTITY MIS-MATCH", C53 shows DRF COMPLETE whereas it should show DRF INCOMPLETE

The actual requirement is that C53 should show DRF INCOMPLETE if C55 = QUANTITY MIS-MATCH. But this is only and only if C14 = Partial Batch Decommissioning. If C14 = "Full Batch Decommissioning with Rework" or "Full Batch Decommissioning without Rework", then C55 criteria should not be considered.

 

[SanjayGMusafir]

The actual requirement is that if C6="USA", then C11 should not be blank

The actual requirement is that if C14 = "Partial Batch Decommissioning", then C20 should not be blank

The above are not set by the formula I gave. How you are deriving them only you know.

My effort was to give you formula for details you have provided - I have checked them again, Its working fine for the conditions in OP #1

 

[SanjayGMusafir]

The actual requirement is that E53 should show DRF INCOMPLETE if E55 = QUANTITY MIS-MATCH

There is no reference to E55 in OP. It's all together a different question and how you are deriving that is not known to me.

 

[rizwanulhasan]

sorry Sanjay, my bad. Typed E instead of C mistakenly. Now rectified.

 

[rizwanulhasan]

The above are not set by the formula I gave. How you are deriving them only you know.

My effort was to give you formula for details you have provided - I have checked them again, Its working fine for the conditions in OP #1

In my 1st post under points 3 and 4.i>, i mentioned the above 2 points. Apologies if i wasn't able to specify properly.