Remember Me?

#1
October 29th 04, 09:53 PM
 Pantryman Posts: n/a
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

#2
October 29th 04, 10:07 PM
 Frank Kabel Posts: n/a

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post jenniss Excel Discussion (Misc queries) 5 January 6th 05 09:18 PM Jacky D. Excel Discussion (Misc queries) 1 December 16th 04 07:09 PM Dave Charts and Charting in Excel 2 December 13th 04 08:25 PM GRITS Excel Discussion (Misc queries) 1 December 2nd 04 09:30 PM Gordon Excel Discussion (Misc queries) 0 November 26th 04 04:19 PM

All times are GMT +1. The time now is 09:19 PM.