Doug at HAL wrote:
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$200 2=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))