Array Formula

[Doug at HAL]

I am using an array formula to collect numeric data. I would like to use an
array formula to collect text data. My data base has text and numeric. When
I try to collect the text data it comes back with a "0". Formula that I am
using is:

=(SUM((SP_DATA!$B$10:$B$2002=A9)*(SP_DATA!$C$10:$C$2002=B9)*(SP_DATA!$D$10:$D$2002=C9)*(SP_DATA!$F$10:$F$2002=E9)*(SP_DATA!$G$10:$G$2002=D9)*SP_DATA!$AH$10:$AH$2002))

AH10:AM2002 contains text.

 

[hrlngrv]

I am using an array formula to collect numeric data. I would like to use an
array formula to collect text data. My data base has text and numeric. When
I try to collect the text data it comes back with a "0". Formula that I am
using is:

=(SUM((SP_DATA!$B$10:$B$2002=A9)*(SP_DATA!$C$10:$C$2002=B9)*(SP_DATA!$D$10:$D$2002=C9)
*(SP_DATA!$F$10:$F$2002=E9)*(SP_DATA!$G$10:$G$2002=D9)*SP_DATA!$AH$10:$AH$2002))

AH10:AM2002 contains text.

First off, why not use SUMPRODUCT to avoid having to enter the formula
as an array formula. Many have confirmed that SUMPRODUCT calculates
such conditional sums faster than array-entered SUM, so the only
drawback to SUMPRODUCT is a little more typing for the function name.

If AH10:AH2002 contain text that looks like numeric zero, then the
formula above would properly evaluate to zero. On the other hand, if
any of these cells contain text that don't look like numbers, e.g.,
"foobar" or "n/a", then your formula should evaluate to #VALUE!.

What sort of string contents are in AH10:AH2002? What does the
following formula return?

=SUMPRODUCT(--(SP_DATA!$B$10:$B$2002=A9),--(SP_DATA!$C$10:$C$2002=B9),
--(SP_DATA!$D$10:$D$2002=C9),--(SP_DATA!$F$10:$F$2002=E9),--(SP_DATA!$G$10:$G$2002=D9))

 

[Frank Kabel]

[....]

First off, why not use SUMPRODUCT to avoid having to enter the formula
as an array formula. Many have confirmed that SUMPRODUCT calculates
such conditional sums faster than array-entered SUM, so the only
drawback to SUMPRODUCT is a little more typing for the function name.

Hi Harlan
I did some testing a couple of weeks ago and quite interestingly in my tests
I found the following:
I. Formulas used:
1.
=SUMPRODUCT(--('Data'!A1:A10000="A"),--('Data'!B1:B10000="X"),'Data'!C1:C10000)
2. =SUM(IF(('Data'!A1:A10000="A")*('Data'!B1:B10000="X"),'Data'!C1:C10000))
3.
=SUM(IF('Data'!A1:A10000="A",IF('Data'!B1:B10000="X",'Data'!C1:C10000,0)))

And the 'Data' sheet consisted of random entries I got the following
results:
- Slowest formula: Formula 3
- Second best: Formula 2 (5% faster)
- Fastest formula: Formula 3 (9% faster than formula 3)

Repeated this tests many times (>10000 alculations each) and the above are
the averaged results.
BTW the '--' syntax seems to be really the fastest for SUMPRODUCT formulas
(closely followed by using N(..))


Frank

 

[Frank Kabel]

[....]

Hi Harlan
I did some testing a couple of weeks ago and quite interestingly in my tests
I found the following:
I. Formulas used:
1.
=SUMPRODUCT(--('Data'!A1:A10000="A"),--('Data'!B1:B10000="X"),'Data'!C1:C10000)
2. =SUM(IF(('Data'!A1:A10000="A")*('Data'!B1:B10000="X"),'Data'!C1:C10000))
3.
=SUM(IF('Data'!A1:A10000="A",IF('Data'!B1:B10000="X",'Data'!C1:C10000,0)))

And the 'Data' sheet consisted of random entries I got the following
results:
- Slowest formula: Formula 3
- Second best: Formula 2 (5% faster)
- Fastest formula: Formula 3 (9% faster than formula 3)

Too early in the morning. Make this:
- Slowest formula: Formula 2 (TWO)
- Second best: Formula 1 (ONE) (5% faster)
- Fastest formula: Formula 3 (THREE) (9% faster than formula 3)