Counting cells using multiple criteria

[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

 

[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

 

[Guest]

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:

 

[andrew.curley]

Works a treat - many thanks to both of you.

Hope you are keeping well Marcelo - felicidade

 

[Guest]

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

"andrew.curley" escreveu: