compacting UPPER/LOWER & column reference

  • Thread starter Thread starter Pantryman
  • Start date Start date
P

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)))}
Click to expand...

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
 
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
 
Back
Top