Sumif slow

[Brian Matlack]

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

[Chip Pearson]

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"
<[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:#[email protected]...
> 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"
> <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

[Brian Matlack]

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