SumProduct too slow
 

I have a worksheet filled with formulas similiar to this one:

=IF(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))=0,1E-50,(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))))

Where ProductionDate, LineID, and QtyProduced are neamed ranges on a
different tab of the same workbook.

If is very very slow to calculate.

Does anyone have a suggestion on how to speed things up?

Thanks,
Diane

SumProduct too slow
 

If those ranges are larger than what they need to be, it'll take longer.

But is there a real reason why you're replacing 0 with that very small number.
If your =sumproduct() doesn't equal 0, then your formula does the calculation
twice.

If you really have to have that 1e-50 shown, then maybe you can use two
cells--one for the =sumproduct() formula and one for the check if 0

=if(a2=0,1e-50,a2)
(and hide that column (A in my example)

diaare wrote:

I have a worksheet filled with formulas similiar to this one:

=IF(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))=0,1E-50,(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))))

Where ProductionDate, LineID, and QtyProduced are neamed ranges on a
different tab of the same workbook.

If is very very slow to calculate.

Does anyone have a suggestion on how to speed things up?

Thanks,
Diane

--

Dave Peterson

SumProduct too slow
 

"Dave Peterson" wrote:

But is there a real reason why you're replacing 0 with that very small number.
If your =sumproduct() doesn't equal 0, then your formula does the calculation
twice.

Short answer: I inherited this spreadsheet and that is how it was set up.

Long Answer: I think it was so that we can distinguish between a day where
there was no production, and a day in the future that has just not had any
production yet.

So - in other formulas in the worksheet there are if statements that
resemble: if qty produced is 0 then.... These formulas would work on the
1e-50 cells (really no production) yet ignore the zeros (date in the future).

That said, is there a way to tell my sumprodoct formula to return a zero if
there is a zero in the production range, but stay null if the production
range is blank for that specific day?

SumProduct too slow
 

Also, looking at the original formula (quickly) and seeing what it does
(make a 0 value into the near 0 value of 1E-50), it would seem you could
eliminate one of the SUMPRODUCT calculations by using a MIN function call
instead....

=MIN(1E-50,SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*Qt yProduced))

Rick


"Dave Peterson" wrote in message
...
If those ranges are larger than what they need to be, it'll take longer.

But is there a real reason why you're replacing 0 with that very small
number.
If your =sumproduct() doesn't equal 0, then your formula does the
calculation
twice.

If you really have to have that 1e-50 shown, then maybe you can use two
cells--one for the =sumproduct() formula and one for the check if 0

=if(a2=0,1e-50,a2)
(and hide that column (A in my example)

diaare wrote:

I have a worksheet filled with formulas similiar to this one:

=IF(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))=0,1E-50,(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))))

Where ProductionDate, LineID, and QtyProduced are neamed ranges on a
different tab of the same workbook.

If is very very slow to calculate.

Does anyone have a suggestion on how to speed things up?

Thanks,
Diane

--

Dave Peterson

SumProduct too slow
 

by using a MIN function call
perhaps a MAX() would be better for the OP's purposes.

"Rick Rothstein (MVP - VB)" wrote:

Also, looking at the original formula (quickly) and seeing what it does
(make a 0 value into the near 0 value of 1E-50), it would seem you could
eliminate one of the SUMPRODUCT calculations by using a MIN function call
instead....

=MIN(1E-50,SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*Qt yProduced))

Rick


"Dave Peterson" wrote in message
...
If those ranges are larger than what they need to be, it'll take longer.

But is there a real reason why you're replacing 0 with that very small
number.
If your =sumproduct() doesn't equal 0, then your formula does the
calculation
twice.

If you really have to have that 1e-50 shown, then maybe you can use two
cells--one for the =sumproduct() formula and one for the check if 0

=if(a2=0,1e-50,a2)
(and hide that column (A in my example)

diaare wrote:

I have a worksheet filled with formulas similiar to this one:

=IF(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))=0,1E-50,(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))))

Where ProductionDate, LineID, and QtyProduced are neamed ranges on a
different tab of the same workbook.

If is very very slow to calculate.

Does anyone have a suggestion on how to speed things up?

Thanks,
Diane

--

Dave Peterson

SumProduct too slow
 

LOL ... yeah, MAX would be better, wouldn't it.<g

Thanks for spotting that.

Rick


"Duke Carey" wrote in message
...
by using a MIN function call
perhaps a MAX() would be better for the OP's purposes.

"Rick Rothstein (MVP - VB)" wrote:

Also, looking at the original formula (quickly) and seeing what it does
(make a 0 value into the near 0 value of 1E-50), it would seem you could
eliminate one of the SUMPRODUCT calculations by using a MIN function call
instead....

=MIN(1E-50,SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*Qt yProduced))

Rick


"Dave Peterson" wrote in message
...
If those ranges are larger than what they need to be, it'll take
longer.

But is there a real reason why you're replacing 0 with that very small
number.
If your =sumproduct() doesn't equal 0, then your formula does the
calculation
twice.

If you really have to have that 1e-50 shown, then maybe you can use two
cells--one for the =sumproduct() formula and one for the check if 0

=if(a2=0,1e-50,a2)
(and hide that column (A in my example)

diaare wrote:

I have a worksheet filled with formulas similiar to this one:

=IF(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))=0,1E-50,(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))))

Where ProductionDate, LineID, and QtyProduced are neamed ranges on a
different tab of the same workbook.

If is very very slow to calculate.

Does anyone have a suggestion on how to speed things up?

Thanks,
Diane

--

Dave Peterson

SumProduct too slow
 

Sumproduct can be very slow if you have large ranges and/or many
conditions. SUMIF is a lot quicker, but can only use one criteria.
However, if you combine your two conditions into one helper column
like this:

=ProductionDate&LineID,

then you could use SUMIF(helper,G$3&$D5,QtyProduced) to replace your
SUMPRODUCT term, and this would be a lot faster.

Hope this helps.

Pete

On Oct 29, 6:07 pm, diaare wrote:
I have a worksheet filled with formulas similiar to this one:

=IF(SUMPRODUCT((ProductionDate=G$3)*(LineID=$D5)*( QtyProduced))=0,1E-50,(SU*MPRODUCT((ProductionDate=G$3)*(LineID=$D5)* (QtyProduced))))

Where ProductionDate, LineID, and QtyProduced are neamed ranges on a
different tab of the same workbook.

If is very very slow to calculate.

Does anyone have a suggestion on how to speed things up?

Thanks,
Diane