compacting UPPER/LOWER & column reference
 

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.


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile: http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=273738

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.