Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pantryman
 
Posts: n/a
Default Workaround for LOWER(A1:A100)


Hi,

I'm using StarOffice Calc which does not recognize a case ident for a
cell range, i.e. UPPER(F31:F10000)

The problem is that I'm using SUMPRODUCT (case sensitive, also in
Excel) and need to get a count for any hit on D18 (for example AAA) in
F31:F10000 which may contain any combination of AAA, AaA, aaA
...........

SUMIF would work since it's not case sensitive but I'm using more than
one criteria.

The comparable Excel formula is

=SUMPRODUCT(A31:A10000TODAY()-90,F31:F10000=D18,M31:M10000)

I also can't use --

Any ideas?

Thanks!


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

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

SUMPRODUCT is case agnostic as well, but your formula is wrong.

This will work in Excel, but I can't guarantee StarOffice as I don't have it

=SUMPRODUCT(--(A31:A10000TODAY()-90),--(F31:F10000=D18),M31:M10000)

or

=SUMPRODUCT((A31:A10000TODAY()-90)*(F31:F10000=D18)*(M31:M10000))

--

HTH

RP

"Pantryman" wrote in message
...

Hi,

I'm using StarOffice Calc which does not recognize a case ident for a
cell range, i.e. UPPER(F31:F10000)

The problem is that I'm using SUMPRODUCT (case sensitive, also in
Excel) and need to get a count for any hit on D18 (for example AAA) in
F31:F10000 which may contain any combination of AAA, AaA, aaA
..........

SUMIF would work since it's not case sensitive but I'm using more than
one criteria.

The comparable Excel formula is

=SUMPRODUCT(A31:A10000TODAY()-90,F31:F10000=D18,M31:M10000)

I also can't use --

Any ideas?

Thanks!


--
Pantryman
------------------------------------------------------------------------
Pantryman's Profile:

http://www.excelforum.com/member.php...o&userid=15233
View this thread: http://www.excelforum.com/showthread...hreadid=273993



  #3   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Bob Phillips" wrote...
SUMPRODUCT is case agnostic as well, but your formula is wrong.

This will work in Excel, but I can't guarantee StarOffice as I don't have

it

=SUMPRODUCT(--(A31:A10000TODAY()-90),--(F31:F10000=D18),M31:M10000)

or

=SUMPRODUCT((A31:A10000TODAY()-90)*(F31:F10000=D18)*(M31:M10000))


I have SO/OOo, and this type of SUMPRODUCT formula won't work in SO/OOo
Calc. With 1, 2, 3, 4 in A1:A4, 1, 10, 100, 1000 in B1:B4, the formula

=SUMPRODUCT((A1:A42)*B1:B4)

returns #VALUE! in SO/OOo whether entered normally or as an array formula.
SO/OOo array capabilities are limited to ranges as operands and arguments to
MDETERM, MINVERSE, MMULT and TRANSPOSE only. There's no support for derived
array expressions.


  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

Hi Harlan,

Thanks for that update.

I assume that array SUM(IF ... works okay. Is that correct?

Bob


"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
SUMPRODUCT is case agnostic as well, but your formula is wrong.

This will work in Excel, but I can't guarantee StarOffice as I don't have

it

=SUMPRODUCT(--(A31:A10000TODAY()-90),--(F31:F10000=D18),M31:M10000)

or

=SUMPRODUCT((A31:A10000TODAY()-90)*(F31:F10000=D18)*(M31:M10000))


I have SO/OOo, and this type of SUMPRODUCT formula won't work in SO/OOo
Calc. With 1, 2, 3, 4 in A1:A4, 1, 10, 100, 1000 in B1:B4, the formula

=SUMPRODUCT((A1:A42)*B1:B4)

returns #VALUE! in SO/OOo whether entered normally or as an array formula.
SO/OOo array capabilities are limited to ranges as operands and arguments

to
MDETERM, MINVERSE, MMULT and TRANSPOSE only. There's no support for

derived
array expressions.




  #5   Report Post  
hgrove
 
Posts: n/a
Default


Bob Phillips wrote...
Thanks for that update.

I assume that array SUM(IF ... works okay. Is that correct?

...

Why would you assume that? The result of such an inner IF expression
would be a derived array, and as such isn't supported by SO/OOo Calc.

When it comes to conditional summing, SO/OOo Calc is very primitive.
Only the 123R2-like DSUM and other list functions are useful for
multiple condition sums and other aggregation calculations.

AFAIK, only gnumeric provides array formula functionality similar to
Excel. Quattro Pro and Xess provide different array functionality (and
I'll take Xess's @CSUM over Excel's SUMPRODUCT any time/every time),
and 123 provides much, much better database functions.

As a spreadsheet, SO/OOo Calc functionality falls somewhere between
Excel and the spreadsheet in Works. Not ready for real business use.


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=273993

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



All times are GMT +1. The time now is 06:33 AM.

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

About Us

"It's about Microsoft Excel"