Sumproduct for 23000 records?

  • Thread starter Thread starter Mycotopian
  • Start date Start date
M

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?
 
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
 
I wish it were that easy. I tried that but I beleive the SUMPRODUC
function has limits because I get a #REF erro
 
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
 
Back
Top