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

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$400 02),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


--
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

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