Excel 2007 - Sum Product help

[plsimmo]

I recently began using the Sum Product formula. However i have noticed that this utilizes ALOT of processing power. I this normal, or do i need to change something in my settings. I have once workbook with two sheets. One has the raw data on it. The other has a matrix that contains the SumProduct formula. ie =SUMPRODUCT(--(Sheet2!$E:$E=Sheet1!$A3),--(Sheet2!$C:$C=Sheet1!C$1),(Sheet2!$I:$I))

I only have 1776 cells with this formula in it and cannot work out why the file size is 2,525KB and hammers the PC.

Any advice appreciated.
Thanks
Paul

 

[wigi]

You are correct, even more when you use entire columns. Did you know how many rows an Excel 2007 spreadsheet can have?

Use pivot tables for these kind of analyses.

 

[T. Valko]

I recently began using the Sum Product formula. However i have noticed that this utilizes ALOT of processing power. I this normal, or do i need to change something in my settings. I have once workbook with two sheets. One has the raw data on it. The other has a matrix that contains the SumProduct formula. ie =SUMPRODUCT(--(Sheet2!$E:$E=Sheet1!$A3),--(Sheet2!$C:$C=Sheet1!C$1),(Sheet2!$I:$I))

I only have 1776 cells with this formula in it and cannot work out why the file size is 2,525KB and hammers the PC.

Any advice appreciated.
Thanks
Paul

A couple of points...

Unless you have data in EVERY row of your sheet DO NOT use entire columns as range references in the SUMPRODUCT function.

Since you're apparently using Excel 2007 or later you use the SUMIFS function to do what you want *and* you CAN use entire columns as range references with SUMIFS!

=SUMPRODUCT(--(Sheet2!$E2:$E1000=Sheet1!$A3),--(Sheet2!$C2:$C1000=Sheet1!C$1),Sheet2!$I2:$I1000)

=SUMIFS(Sheet2!$I:$I,Sheet2!$E:$E,Sheet1!$A3,Sheet2!$C:$C,Sheet1!C$1)

 

[plsimmo]

A couple of points...

Unless you have data in EVERY row of your sheet DO NOT use entire columns as range references in the SUMPRODUCT function.

Since you're apparently using Excel 2007 or later you use the SUMIFS function to do what you want *and* you CAN use entire columns as range references with SUMIFS!

=SUMPRODUCT(--(Sheet2!$E2:$E1000=Sheet1!$A3),--(Sheet2!$C2:$C1000=Sheet1!C$1),Sheet2!$I2:$I1000)

=SUMIFS(Sheet2!$I:$I,Sheet2!$E:$E,Sheet1!$A3,Sheet2!$C:$C,Sheet1!C$1)

Thanks Biff. Appreciated.

 

[T. Valko]

A couple of points...

Unless you have data in EVERY row of your sheet DO NOT use entire columns as range references in the SUMPRODUCT function.

Since you're apparently using Excel 2007 or later you use the SUMIFS function to do what you want *and* you CAN use entire columns as range references with SUMIFS!

=SUMPRODUCT(--(Sheet2!$E2:$E1000=Sheet1!$A3),--(Sheet2!$C2:$C1000=Sheet1!C$1),Sheet2!$I2:$I1000)

=SUMIFS(Sheet2!$I:$I,Sheet2!$E:$E,Sheet1!$A3,Sheet2!$C:$C,Sheet1!C$1)

Just to add a bit...

Array formulas and SUMPRODUCT formulas will calculate EVERY cell they reference. So, if you use entire columns as range references and only have data in 1000 rows, array formulas and SUMPRODUCT formulas will still evaluate from row 1001 to the last row in the sheet. Since this can only happen in Excel 2007 or later that means you're calculating millions of unused cells!

OTOH, a function like SUMIFS will only calculate up to the used range. Even though you use entire columns as range references the SUMIFS function would only calculate up to row 1000.

 

[T. Valko]

Thanks Biff. Appreciated.

You're welcome!

 

[Matty]

Hi Biff,

OTOH, a function like SUMIFS will only calculate up to the used range. Even though you use entire columns as range references the SUMIFS function would only calculate up to row 1000.

That's interesting and wasn't something I was aware of. Do you know why SUMIF and SUMIFS do this yet SUMPRODUCT/array alternatives don't?

My question is linked to a discussion taking place here:

http://www.mrexcel.com/forum/showthread.php?t=564780

Cheers,

Matty

 

[T. Valko]

Hi Biff,

That's interesting and wasn't something I was aware of. Do you know why SUMIF and SUMIFS do this yet SUMPRODUCT/array alternatives don't?

My question is linked to a discussion taking place here:

http://www.mrexcel.com/forum/showthread.php?t=564780

Cheers,

Matty

Don't know why. Only the folks at MS who programmed it that way would know the "why".

I read the posts in the other thread...

This site has lots of good info relating to efficiency:

http://www.decisionmodels.com/

 

[Matty]

Thanks Biff.

I've used Charles Williams' site before - there's some very useful stuff on there. I'll have to reacquaint myself with it.

Cheers,

Matty

 

[T. Valko]

Thanks Biff.

I've used Charles Williams' site before - there's some very useful stuff on there. I'll have to reacquaint myself with it.

Cheers,

Matty

It's kind of hard to explain (in a few sentences) when it's not a good idea to use volatile functions. Most of the time it's probably just fine.

The easiest way I can tell when you shouldn't use volatile functions is...

When you type something in a cell then hit Enter and you notice a "delay" in the amount of time it takes for the entry to register and the cell selector to move to the next cell (if you have it set to do that).

That's a sure sign that you need to think about more efficient methods!