Need HELP with ARRAY Formula

  • Hi,

    I am needing to find the 'MAX' date in the 'Edit Date' column when the 'New Value' column is equal to 'Tech Review'.

    There are two case numbers that have two different dates for that scenario and I need to capture the latest or 'MAX' date vs the first date.


    on my StatTbl tab I have the formula in column D:


    {=INDEX(RawData[Edit Date],MATCH(1,("Tech Review"=RawData[New Value])*([@[UniqueCase'#]]=RawData[Case Number]),0))}


    I cannot figure out how to grab the MAX of the value I am trying to populate in that cell.

    Any help would be appreciated.


    see attached

    Finding MAX Date.xlsx

  • CS


    It has been reported that that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.


    Forum Etiquette


    4b. Do not cross-post without supplying a link to the duplicate question on the other Forum.

  • Good Afternoon,

    Sorry for delay...


    Yes, I did and it works. Thank You!


    Now, when the formula finds doesn't find a match it provides a date of 1/0/1900. I want those to be blank but cannot get any IF statement to work.

    Any ideas?

    Thanks so much

  • Hi Glenn,

    I have another question regarding the solution you provided.


    In the formula

    =SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[New Value]="Tech Review")*(RawData[Edit Date])))


    How would I revise the formula if I am looking for more than one value in the red text above. Meaning, the New Value could be "Tech Review", "Customer Related", "Not Customer Related" ?


    Thanks in advance for any assistance.

    Hope you and family still doing ok through this crazyness!

    /cristy

  • Hi Cristy,


    still doing OK thanks ... hope you and family are too.


    Try this:
    =SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[New Value]={"Tech Review","Customer Related","Not Customer Related"})*(RawData[Edit Date])))

  • Hi Glenn,

    Hope you are still doing well. Praying for your Prime Minister...


    I need help again. Hoping you might be able to assist.


    I now need to calculate the MIN date and the formula you provide (with SUMPRODUCT) doesn't seem to work when trying to find MIN vs MAX.


    On the attached:

    RawData tab has new Column for A - "Calculated New Value"

    This captures any value in the "New Value" column to show "eMotive" if equal to "Jennifer Reed", "App - Premium Offer", or "App - Non Premium Offer".

    I now need to capture the FIRST date (or MIN date) of the "Edit Date" field if "Calculated New Value" = "eMotive".


    The formula would be entered in the StatTbl tab/Column C (eMotive).



    When the formula in StatTbl tab/Column C (eMotive) has:

    =IF(SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date])))=0,"",SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date]))))

    The eMotive date is populated.


    If formula in StatTbl tab/Column C (eMotive) has:

    =IF(SUMPRODUCT(MIN(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date])))=0,"",SUMPRODUCT(MIN(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date]))))

    The eMotive date is BLANK


    I have done some reading and read that the MIN and SUMPRODUCT don't always play nice. Everything I have tried does not work. Any ideas?

    Thanks (again...) for your help.

    Take care.


    see attached

    Finding MIN Date.xlsx