Workaround for LOWER(A1:A100)

[Pantryman]

Hi,

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

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

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

The comparable Excel formula is

=SUMPRODUCT(A31:A10000>TODAY()-90,F31:F10000=D18,M31:M10000)

I also can't use --

Any ideas?

Thanks

 

[Bob Phillips]

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:A10000>TODAY()-90),--(F31:F10000=D18),M31:M10000)

or

=SUMPRODUCT((A31:A10000>TODAY()-90)*(F31:F10000=D18)*(M31:M10000))

 

[Harlan Grove]

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:A10000>TODAY()-90),--(F31:F10000=D18),M31:M10000)

or

=SUMPRODUCT((A31:A10000>TODAY()-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:A4>2)*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.

 

[Bob Phillips]

Hi Harlan,

Thanks for that update.

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

Bob

 

[hgrove]

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 expressio
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 fo
multiple condition sums and other aggregation calculations.

AFAIK, only gnumeric provides array formula functionality similar t
Excel. Quattro Pro and Xess provide different array functionality (an
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 betwee
Excel and the spreadsheet in Works. Not ready for real business use