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
--
Regards
Frank Kabel
Frankfurt, Germany
Pantryman wrote:
I'm at
{=IF(C20="","",SUM(SUMPRODUCT(A31:A10000
TODAY()-90,F31:F10000=UPPER(D20),M31:M10000),SUMPRODUCT(A3 1 :A10000
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 I
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 to
present all options in a SUMPRODUCT and that seems to be begging for
an easier option.
Thanks,
Marinus.
|