compacting UPPER/LOWER & column reference

[Pantryman]

I'm at

{=IF(C20="","",SUM(SUMPRODUCT(A31:A10000
TODAY()-90,F31:F10000=UPPER(D20),M31:M10000),SUMPRODUCT(A31 :A1000

TODAY()-90,F31:F10000=LOWER(D20),M31:M10000)))}

Any ideas how I can shrink this one?
The idea is to accept aaa, AAA, aAa or any other combination but
realize this only covers aaa and AAA.

The thing is that I'm not able to match it with D20;

=UPPER(IF(ISBLANK(C19),"",IF(ISERROR(OFFSET(U2
MATCH(C19,U3:U17,0),-2)),"DEFINE",OFFSET(U2
MATCH(C19,U3:U17,0),-2))))

since the column is considered as-case.

I.e., if F40=Aaa and the result the last formula = AAA I need t
present all options in a SUMPRODUCT and that seems to be begging for a
easier option.

Thanks,
Marinus

 

[Frank Kabel]

Hi
no need for UPPER / LOWEr. Simply use
=IF(C20="","",SUMPRODUCT(--(A31:A10000>
TODAY()-90),--(F31:F10000=D20),M31:M10000))

also need for array entry