Formula to long

[Scott@CW]

I need to use this formula in excel 2002, but I am getting the error about it
being to long any ideas?

=SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220210"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220810"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220220"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220310"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2210220320"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220410"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220420"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220421"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220821"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220422"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220510"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220520"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220110"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220120"))+SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(DATA!$M$2:$M$19000="2110220121"))

 

[T. Valko]

Yikes!

Make a list of the numbers? you want to count for:

2110220210
2110220810
2110220220
2110220310
2210220320
etc
etc

Then:

=SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(ISNUMBER(MATCH(DATA!$M$2:$M$19000,numbers_list,0))))

When you enclose numbers in quotes:"2110220210", Excel evaluates them as
TEXT, so:

"2110220210"
2110220210

are not equal.

Biff

 

[Lori]

=SUMPRODUCT((DATA!$A$2:$A$19000="DEC06")*(DATA!$D$2:$D$19000="Frontline")*
(DATA!$M$2:$M$19000={"2110220210","2110220110","2110220120","2110220121",
"2110220220","2110220310","2110220410","2110220420","2110220421","2110220422",
"2110220510","2110220520","2110220810","2110220821","2210220320"}))

Note:OR can't be used as it evaluates to a single value when used with
arrays

 

[Bob Phillips]

Put the codes in a range, say N1:N20, and use

=SUMPRODUCT(--(DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(ISNUMBER(MATCH(DATA!$M$2:$M$19000,N1:N20,0))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

The braces indicate an array constant, so that the range is compared against
all values in that array.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)