max out SUMPRODUCT

[dkubiak]

These seems to a limit on the number of rows that SUMPRODUCT will consider. I have 317,706 rows, and I just get an #N/A response when I try to use all those rows:

=SUMPRODUCT(--(R2:R317706=AJ2),--(M2:M317706=AK1))

Am I doing something wrong?

 

[Jonmo1]

Works for me..

Are there any #N/A errors within either of the referenced ranges (column R or M) ?

 

[dkubiak]

That was it. A couple of VLOOKUPs were returning #N/A in column R. Thank you for your help.

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[T. Valko]

That was it. A couple of VLOOKUPs were returning #N/A in column R. Thank you for your help.

Since you're doing straight comparisons on a huge range the COUNTIFS function will be more efficient (and, it will ignore any error values in the ranges).

=COUNTIFS(R2:R317706,AJ2,M2:M317706,AK1)

 

[dkubiak]

Thank you for the advice on the COUNTIFS.

I'll use that going forward.

 

[T. Valko]

Thank you for the advice on the COUNTIFS.

I'll use that going forward.

You're welcome. Thanks for the feedback!