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

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

"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.

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.

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