Formula to long
 

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

Formula to long
 

Have your tried

=sumproduct(--DATA!$A$2:$A$19000="DEC06"),--(DATA!$D$2:$D$19000="Frontline"),--(OR(DATA!$M$2:$M$19000="2110220210",DATA!$M$19000= "2110220810", etc....

I don't presume you're looking for all accounts starting with 2110220.
If that was the case, you could do (LEFT(DATA!$M$2:$M$19000,7)=2110220)

That should work, I think...

"Scott@CW" wrote:

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

Formula to long
 

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

"Scott@CW" wrote in message
...
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"))

Formula to long
 

=SUMPRODUCT((DATA!$A$2:$A$19000="DEC06")*(DATA!$D$ 2:$D$19000="Frontline")*
(DATA!$M$2:$M$19000={"2110220210","2110220110","21 10220120","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

Scott@CW wrote:
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"))

Formula to long
 

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)


"Scott@CW" wrote in message
...
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"))

Formula to long
 

That worked perfectly, thank you very much. If you dont mind when you are
using "{" what is that telling the formula in english?

"Lori" wrote:

=SUMPRODUCT((DATA!$A$2:$A$19000="DEC06")*(DATA!$D$ 2:$D$19000="Frontline")*
(DATA!$M$2:$M$19000={"2110220210","2110220110","21 10220120","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

Scott@CW wrote:
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"))

Formula to long
 

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)


"Scott@CW" wrote in message
...
That worked perfectly, thank you very much. If you dont mind when you are
using "{" what is that telling the formula in english?

"Lori" wrote:

=SUMPRODUCT((DATA!$A$2:$A$19000="DEC06")*(DATA!$D$ 2:$D$19000="Frontline")*
(DATA!$M$2:$M$19000={"2110220210","2110220110","21 10220120","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

Scott@CW wrote:
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"))