SumProduct too slow

[diaare]

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]

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]

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?

 

[Rick Rothstein \(MVP - VB\)]

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)*QtyProduced))

Rick

 

[Duke Carey]

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

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)*QtyProduced))

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

Thanks for spotting that.

Rick

by using a MIN function call

perhaps a MAX() would be better for the OP's purposes.

 

[Pete_UK]

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