Help in Formula

[cyliyu]

I need help in the formula in "Check" sheet Column "L" & "M".
I wanted to calculate and display the "Return Date" and "Expiry Date" only when the following criteria meet.
1) The Status in the "RawData" sheet = Closed &
2) Serviceable Days in the "Check" sheet = In Service

 

[bosco_yip]

Try,

1] L6, copied down :

=IF(($C6<>"")*($K6="In Service"),SUMIFS(RawData!$C:$C,RawData!$D:$D,$C6,RawData!$E:$E,$D6,RawData!I:I,"Closed"),"")

2] M6, copied down :

=IF(L6="","",EDATE(L6,3))

Regards
Bosco

 

[cyliyu]

Thanks, Bosco.

It works. Let me try on my master spreadsheet when back to office.
Appreciate your help and fast response.

 

[cyliyu]

Hi, Bosco,

Trying to make amendment to your formula so that if
1) The Status in the "RawData" sheet = Closed OR
2) Serviceable Days in the "Check" sheet = In Service
Then display the "Return Date" and "Expiry Date".

But without success. would you be able to help?

 

[NARAYANK991]

Hi ,

See if this is OK. If not , manually enter the required output dates in column L , and then upload your workbook.

Narayan

 

[cyliyu]

Hi Narayank991,

I can't get it works in my spreadsheet.
If I added a "=" sign in the >0 , it works but other rows will not work.
Need your help again. Thanks.

 

[NARAYANK991]

Hi ,

I am sorry , but without understanding the logic of what you want , I cannot help.

If you can explain clearly what you want done , I can suggest a formula which does that.

Narayan

 

[cyliyu]

Hi,

I am sorry about my unclear explanation.

The intend was to check if the product serial number still "In Service" and match the "Status" - CLOSED in the RawData sheet OR
If the product serial number is not "In Service" - Can be => to 0 day, and match the "Status" - CLOSED in the RawData Sheet.

Then output the Return Date/Expiry Date/Warranty

I managed to amend your formula and it seems to work fine. I removed
(RawData!$D$6:$D$298=$F88) => because it checked the date in both sheets which both may not be necessary on the same date.

=IF(($C88<>"")*(($L88="In Service") + (SUMPRODUCT((RawData!$D$6:$D$298=$F88)*(RawData!$G$6:$G$298 ="Closed") * ($C88=RawData!$E$6:$E$298)*($C88=RawData!$F$6:$F$298))>0)),SUMIFS(RawData!$D:$D,RawData!$E:$E,$C88,RawData!$F:$F,$D88,RawData!G:G,"Closed"),"")

The only issues now is that if say 00277/1017 occurred 2 times, both in row 63 and row 82 will display the info. which it supposes to display only at row 82, meaning only the last record will be displayed and all previous record will be ignored.
Is it possible?

 

[NARAYANK991]

Hi ,

I am still not clear on your requirement.

Check this file , and give your feedback.

Narayan

 

[cyliyu]

Thanks, Narayank991.

The spreadsheet is working as per my requirement.
Noticed in order to make it works, helper column "Q" and "R" was added.
Would you be able to elaborate why this 2 column is needed in order to make it works?

Basically, it was used to track all the repaired product as stated in the RawData, a product can be sent out and back a few times for repair, status "Closed" means repaired and with us, "Open" means returned to the supplier for repair.

So, we need to plant this product into the CAR and monitor the performance.
This is where the "Check" sheet was created.

From here, we will need to track the product that was with us whether in service or not and their warranty. Those return for repair will be ignored until it returns to us again.

 

[NARAYANK991]

Hi ,

The helper columns have been added only to make it easier to verify whether entries fulfill all the requirements.

The formula in column Q is :

=IF(COUNTIFS($C$6:$C$1050, C6, $D$6:$D$1050, D6) > COUNTIFS($C$6:$C6, C6, $D$6:$D6, D6), "", 1)

What this is checking for is whether the combination of serial number and date code in columns C and D is unique or whether there are multiple such entries ; if there are multiple entries only the last entry will be flagged with 1 , and earlier entries will have blanks.

The formula in column R is :

=IF(Q6 = 1, IF(SUMPRODUCT((RawData!$G$6:$G$298 ="Closed") * (RawData!$E$6:$E$298 = C6) * (RawData!$F$6:$F$298 = D6)) > 0, 1, ""),"")

which checks for whether there is a corresponding entry in the RawData tab , which has the same serial code and date code and a status of Closed.

These two helper column formulae can be incorporated in the main formula in column M , but then this formula will become a lengthy one , and will not be readable. Splitting the formula using helper columns makes it easy to read and visualize where things have gone wrong if the output in column M is not what it should be.

Narayan

 

[cyliyu]

Hi, Narayan.

Noted with many thanks.
May I have a last request? could you showed me how this 2 helper column can be incorporated into one main formula?

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[cyliyu]

Thanks, Narayan.

Got it now.
Appreciate your time and help.