Counting cells using multiple criteria

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

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
andrew.curley wrote:
> 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
>
>
> --
> andrew.curley
> ------------------------------------------------------------------------
> andrew.curley's Profile: http://www.excelforum.com/member.php...o&userid=35326
> View this thread: http://www.excelforum.com/showthread...hreadid=550996

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:

>
> 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
>
>
> --
> andrew.curley
> ------------------------------------------------------------------------
> andrew.curley's Profile: http://www.excelforum.com/member.php...o&userid=35326
> View this thread: http://www.excelforum.com/showthread...hreadid=550996
>
>

Works a treat - many thanks to both of you.

Hope you are keeping well Marcelo - felicidades

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

"andrew.curley" escreveu:

>
> Works a treat - many thanks to both of you.
>
> Hope you are keeping well Marcelo - felicidades
>
>
> --
> andrew.curley
> ------------------------------------------------------------------------
> andrew.curley's Profile: http://www.excelforum.com/member.php...o&userid=35326
> View this thread: http://www.excelforum.com/showthread...hreadid=550996
>
>