Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 29th 04, 09:53 PM
Pantryman
 
Posts: n/a
Default 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


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

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
what formula do i put for column m = column k minus column l in e. jenniss Excel Discussion (Misc queries) 5 January 6th 05 08:18 PM
Putting text in a column based on variable text from another colum Jacky D. Excel Discussion (Misc queries) 1 December 16th 04 06:09 PM
Change the width of a single column in a column chart Dave Charts and Charting in Excel 2 December 13th 04 07:25 PM
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 1 December 2nd 04 08:30 PM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 0 November 26th 04 03:19 PM


All times are GMT +1. The time now is 01:59 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017