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 my 1st post under points 3 and 4.i>, i mentioned the above 2 points. Apologies if i wasn't able to specify properly.

Now it seems to be a multi-layered problem which can't be handled in a single post.

I think someone better than me shall look into and help you better

 

[rizwanulhasan]

Sanjay, my 1st formula in post#1 is working perfectly.

The only criteria left is that when C14="Partial Batch Decommissioning", C53 should show DRF COMPLETE only if C55="QUANTITY MATCH".

But despite C55 <> "QUANTITY MATCH", C53 shows DRF COMPLETE

="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")

So can you please look into that 1 issue?

 

[SanjayGMusafir]

But despite C55 <> "QUANTITY MATCH", C53 shows DRF COMPLETE

Try this -

="DRF"&IFS(AND(COUNTIFS(C5:C10,"<>")=6,C14<>"",C32<>"",C40<>"",C43<>"",C44<>"",C46<>"",C47<>"",LEN(C8)=14,C55="QUANTITY MATCH",OR(AND(C6="USA",C11=""),AND(C14="Partial Batch Decommissioning",OR(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]

First part of my comments in post 16 now works fine i.e.

The actual requirement is that C53 should show DRF INCOMPLETE if C55 = QUANTITY MIS-MATCH.

However, the below is not met

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.

Because, C55 criteria is still being considered despite C14 = "Full Batch Decommissioning with Rework" or "Full Batch Decommissioning without Rework"

 

[SanjayGMusafir]

First part of my comments in post 16 now works fine i.e.

The actual requirement is that C53 should show DRF INCOMPLETE if C55 = QUANTITY MIS-MATCH.

However, the below is not met

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.

Because, C55 criteria is still being considered despite C14 = "Full Batch Decommissioning with Rework" or "Full Batch Decommissioning without Rework"

I told you it's a multi-layered problem now. Hope someone better than me takes note of it and helps you in a better way.

 

[rizwanulhasan]

I told you it's a multi-layered problem now. Hope someone better than me takes note of it and helps you in a better way.

Ok Sanjay. But thanks for all your help.