Forum Discussion

waglagla's avatar
waglagla
Copper Contributor
Nov 09, 2019

calculate datedif if mutiples conditions within a range are met

Dear Community, hopefully someone can help with the issue I am having. I am trying to calculate the days between a certain date and today's date if multiples conditions are met.  =IF(AND(D3:D77="...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 10, 2019

waglagla 

Small sample file worth many words. Wyn make one assumption, I bit another:

=IFNA(TODAY()-INDEX($D:$D,MATCH(1,INDEX( ($F:$F="NKD")*($E:$E="")*($D:$D>1),0),0)),"")

not sure what did you mean exactly.

waglagla's avatar
waglagla
Copper Contributor
Nov 10, 2019

Wowwwww, this is exactly what I was looking for. I don't understand the formula but its working.
Looks like a foreign language to me, i got so much to learn !!
thank youuu Sergei 
SergeiBaklan 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 11, 2019

    waglagla , you are welcome.

     

    The multiplication

    ($F:$F="NKD")*($E:$E="")*($D:$D>1)

    check you criteria for each row from 1 to last in the sheet. Since TRUE is equal to 1 and FALSE to 0, it returns 1 only for the row where all three criteria are met. Other words you have sequence like {0,0,0,1,0...}. Inner INDEX which wrap this multiplication return this entire array to next function since we use INDEX(...,0) with zero as second parameter.

    Next MATCH(1, ... ,0) finds the exact position (last parameter =0) of number 1 in this array. Actually that's the number of the row for which all criteria met.

    Having this position we extract the date in column D with outer INDEX from received row number, and subtract this date from TODAY().

    If there is no row for which all criteria are met, above formula returns #N/A error. IFNA checks if such error appears and returns empty string (last parameter), otherwise result of the calculation.

     

    Above is not strong explanation but hope helps to understand the formula.

Resources