multipule sumproduct formula errors

  • I want a cell to automatically display a text (T1, T2, T3, T4) based on whether or not the input in another cell is found in a table. (ie 020 = T2; 310 = T3 ect)


    =IF(SUMPRODUCT(--(C3=I3:I16))>0,"T1") will list for just one category, but I cant get the formula to accept multiple arrays AND specific inputs when the value is in that array.
    =IF(SUMPRODUCT(--(C3=I3:I16))>0,"T1")*(--(C3=J3:J16))>0,"T2") ect. What am I doing wrong?


    *this is for tracking workload, I want the claim identifier to trigger the category input so a separate calculation will populate the workload credit.

  • Attach a sample workbook. Remember to desensitise it first.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • this should be the unprotected template. I want the week 1/week 2 tabs to automatically calculate points per EP/Contentions/Tier the way the credit tracker tab does. The standards tab contains the reference tables used for look up. I tried an Aggregate formula last night but keep getting the NUM! error.

  • It seems your named range in the formula has a prefixed "e", but you don't have a named range called "eEPs".
    Try changing the reference in the formula to "EPs".

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Perhaps?


    =INDEX(standards!$I$2:$L$2,SUMPRODUCT(--(standards!$I$3:$L$38=C3)*(COLUMN(standards!$I$3:$L$38)-COLUMN(standards!$I$3:$I$38)+1)))


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!