Sumif slow
 

Hi!

I have a table of 7200 sumif statements (R 200 by C 36). They use
column A and Row 1 as criteria to show me qty of parts shipped by month
for a 3 year period.
A B C
1|Part # |Jan-03|Feb-03|etc.
2|25-125 31 25

Problem is it takes my computer about 90 seconds to calculate all these
statements. Is there a better way? Here is the formula I use in each
cell.

=SUMIF('2003'!$L$3:$L$56085,$A5&"1",'2003'!$B$3:$B $56085)
A5 contains the part #, and "1" is for January and so-on

Any suggestions would be great!! Thanks for your time!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=545636

Sumif slow
 

I don't know if the following would be faster. Give it a test on
a backup copy of your workbook.

=SUMPRODUCT(('2003'!$L$3:$L$56085=$A5)*('2003'!$B$ 3:$B$B56085))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Brian Matlack"

wrote in message
news:Brian.Matlack.28dpha_1148586601.7924@excelfor um-nospam.com...

Hi!

I have a table of 7200 sumif statements (R 200 by C 36). They
use
column A and Row 1 as criteria to show me qty of parts shipped
by month
for a 3 year period.
A B C
1|Part # |Jan-03|Feb-03|etc.
2|25-125 31 25

Problem is it takes my computer about 90 seconds to calculate
all these
statements. Is there a better way? Here is the formula I use in
each
cell.

=SUMIF('2003'!$L$3:$L$56085,$A5&"1",'2003'!$B$3:$B $56085)
A5 contains the part #, and "1" is for January and so-on

Any suggestions would be great!! Thanks for your time!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile:
http://www.excelforum.com/member.php...fo&userid=3508
View this thread:
http://www.excelforum.com/showthread...hreadid=545636

Sumif slow
 

My tests have shown that SUMPRODUCT is some orders of magnitude slower than
SUMIF, so I think the problem is just the sheer volume of calculations and
the sped (or lack of it) of your machine.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
I don't know if the following would be faster. Give it a test on
a backup copy of your workbook.

=SUMPRODUCT(('2003'!$L$3:$L$56085=$A5)*('2003'!$B$ 3:$B$B56085))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Brian Matlack"

wrote in message
news:Brian.Matlack.28dpha_1148586601.7924@excelfor um-nospam.com...

Hi!

I have a table of 7200 sumif statements (R 200 by C 36). They
use
column A and Row 1 as criteria to show me qty of parts shipped
by month
for a 3 year period.
A B C
1|Part # |Jan-03|Feb-03|etc.
2|25-125 31 25

Problem is it takes my computer about 90 seconds to calculate
all these
statements. Is there a better way? Here is the formula I use in
each
cell.

=SUMIF('2003'!$L$3:$L$56085,$A5&"1",'2003'!$B$3:$B $56085)
A5 contains the part #, and "1" is for January and so-on

Any suggestions would be great!! Thanks for your time!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile:
http://www.excelforum.com/member.php...fo&userid=3508
View this thread:
http://www.excelforum.com/showthread...hreadid=545636

Sumif slow
 

Thanks for the input Chip & Bob! I'll keep working at it.


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=545636