CODEWITHSUNDEEP
arraysexcelexcel-formulaconditional-statementscell
franz punzalan
franz punzalan

Reputation: 1

EXCEL FORMULA. version 2019

I am hitting a wall on my formula and don't know how to progress anymore. your help is much appreciated.

The table is complicated and submitted in this pattern, so i want to add a formula wherein the result will calculate if the parameters are met even the rows are multiple for a same salary but within the date range of SAP_MOSAL.

Parameters:

  1. If SR_MOSAL is found in column SAP_MOSAL, then pass_1, if not then "FOR UPDATING"
  2. if pass_1 then SR_FDATE must be within SAP_FDATE and SAP_TDATE, then pass_2, if not then "FOR UPDATING"
  3. If Pass_1 and Pass_2 even with multiple rows, then "RETAIN"

Image:
enter image description here

a formula to solve this would be helpful :)

Upvotes: 0

Views: 59

Answers (1)

Black cat
Black cat

Reputation: 6271

The formula in G2 and drag down.

If not 365 use only the inner function.

=LET(cnt,IF(SUMPRODUCT(IF((@Table2[SR_MOSAL]=Table2[SAP_MOSAL])*
(@Table2[SR_FDATE]>=Table2[SAP_FDATE])*
(@Table2[SR_FDATE]<=Table2[SAP_TDATE]),1,0))>0,"RETAIN","FOR UPDATING"),
cnt)

Inner function

=IF(SUMPRODUCT(IF((@Table2[SR_MOSAL]=Table2[SAP_MOSAL])*
(@Table2[SR_FDATE]>=Table2[SAP_FDATE])*
(@Table2[SR_FDATE]<=Table2[SAP_TDATE]),1,0))>0,"RETAIN","FOR UPDATING")

enter image description here

Upvotes: 1

Related Questions