Sumproduct for 23000 records?

[Mycotopian]

=SUMPRODUCT(('Detailed View'!$B$6:$B$8998=B6)*('Detailed
View'!$F$6:$F$8998="Completed"))

Hey guys this is my current equation I use on a monthly report which
always only includes a few thousand records. However when I run the
same report (in query analyzer) for the entire year there are about
23000 records returned. This presents a problem since my current
formula can only handle about 9000 records. This formula works
perfectly I just need to increase the capacity of it to handle a larger
amount of records.

Any Ideas?

 

[Frank Kabel]

Hi
just change the range in your foormula. e.g.,
=SUMPRODUCT(('Detailed View'!$B$6:$B$23000=B6)*('Detailed
View'!$F$6:$F$23000="Completed"))

HTH
Frank

 

[Mycotopian]

I wish it were that easy. I tried that but I beleive the SUMPRODUC
function has limits because I get a #REF erro

 

[Mycotopian]

Disregard previous post. I got it working. Thanks Frank

 

[Frank Kabel]

Hi
AFAIK the limit for SUMPRODUCT if 65535 rows. I tried the formula and
it works. Is there a #REF error in one of the columns SUMPRODUCT
evaluates?

Frank

 

[Frank Kabel]

hi
good to hear
Frank