Counting cells using multiple criteria

A

andrew.curley

I have been counting the occurance of model numbers using multiple
criteria over two or more columns. I've been using SUMIF with multiple
criteria in an array, but my problem is that, because I have 40,000 rows
of data, it takes an age to do the calculation.

Is there an easier/quicker way?

Example:
{=SUM(IF(($K$6:$K$40002=B127)*(LEFT($N$6:$N$40002,2)="PR")*($L$6:$L$40002="V"),1,0))}

Thanks
 
A

aidan.heritage

Look through this forum for examples of the sumproduct formula, which
isn't an array formula - your case translates to:-

=SUMPRODUCT(--($K$6:$K$40002=B127),--(LEFT($N$6:$N$40002,2)="PR"),--($L$6:$L$40002="V"))

will do it for you
 
M

Marcelo

Hi Andrew,

try to use a SUMPRODUCT as:

SUMPRODUCT(($K$6:$K$40002=B127)*(LEFT(($N$6:$N$40002),2)="PR")*($L$6:$L$40002="V"))

hope this helps
regards from Brazil
Marcelo

"andrew.curley" escreveu:
 
A

andrew.curley

Works a treat - many thanks to both of you.:)

Hope you are keeping well Marcelo - felicidade
 
M

Marcelo

thanks for the feedback, felicidades para você também

"andrew.curley" escreveu:
 
Top