Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
I've inhereted a workbook where the previous owner used a complex {=SUM()}
function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external-file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$15000))} -- Hmm...they have the Internet on COMPUTERS now! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
The short answer to your question: it depends. What does it depend on?
Available memory, processor speed, etc. Dave -- Brevity is the soul of wit. "MDW" wrote: I've inhereted a workbook where the previous owner used a complex {=SUM()} function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external-file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$15000))} -- Hmm...they have the Internet on COMPUTERS now! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
Long ago, in a galaxy far away, Excel didn't have a SUMPRODUCT() function,
and the array version of SUM() was the vehicle by which you summed the products of arrays. The array SUM has been stable for as long as I've used it, going on 18 or 19 years. "MDW" wrote: I've inhereted a workbook where the previous owner used a complex {=SUM()} function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external-file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$15000))} -- Hmm...they have the Internet on COMPUTERS now! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
The advantage of SUMPRODUCT is that it doesn't have to be array entered.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "MDW" wrote in message ... I've inhereted a workbook where the previous owner used a complex {=SUM()} function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external -file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$1500 0))} -- Hmm...they have the Internet on COMPUTERS now! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
Maybe it wasn't used in that way, but I would be interested to hear when
Excel didn't have SUMPRODUCT . -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Duke Carey" wrote in message ... Long ago, in a galaxy far away, Excel didn't have a SUMPRODUCT() function, and the array version of SUM() was the vehicle by which you summed the products of arrays. The array SUM has been stable for as long as I've used it, going on 18 or 19 years. "MDW" wrote: I've inhereted a workbook where the previous owner used a complex {=SUM()} function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external -file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$1500 0))} -- Hmm...they have the Internet on COMPUTERS now! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
Bob -
I started using Excel in 1987. It was version 2, I think. Windows was not yet an OS, or at least the PC didn't boot into Windows. You started DOS, then started Windows, THEN you could run Excel. Excel was the only application I owned that ran on Windows. At that time Excel didn't have SUMPRODUCT. However, working in the investment banking field, and calculating weighted averages of all kinds, I was thrilled to be able to create array formulas that worked the way SUMPRODUCT does. A few years later (maybe Excel 5?) introduced SUMPRODUCT. I still use the array form of SUM() out of old habit more than I use SUMPRODUCT Old and not very valuable information, but that's my recollection. Duke "Bob Phillips" wrote: Maybe it wasn't used in that way, but I would be interested to hear when Excel didn't have SUMPRODUCT . -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Duke Carey" wrote in message ... Long ago, in a galaxy far away, Excel didn't have a SUMPRODUCT() function, and the array version of SUM() was the vehicle by which you summed the products of arrays. The array SUM has been stable for as long as I've used it, going on 18 or 19 years. "MDW" wrote: I've inhereted a workbook where the previous owner used a complex {=SUM()} function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external -file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$1500 0))} -- Hmm...they have the Internet on COMPUTERS now! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
Bob Phillips wrote...
Maybe it wasn't used in that way, but I would be interested to hear when Excel didn't have SUMPRODUCT . .... It appeared in either Excel 4 or Excel 5. I don't remember whether 123 or Quattro Pro introduced it, but I'm fairly sure Excel's was in response to its erstwhile competitors'. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
Oh yeah, back then Excel allowed only one sheet per file - they weren't even
called workbooks, just worksheets. You could link between worksheet files, and you could save a group of open worksheets as a 'workspace.' When you opened a workspace, Excel simply cycled through the list of files and opened each in turn. "Bob Phillips" wrote: Maybe it wasn't used in that way, but I would be interested to hear when Excel didn't have SUMPRODUCT . -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Duke Carey" wrote in message ... Long ago, in a galaxy far away, Excel didn't have a SUMPRODUCT() function, and the array version of SUM() was the vehicle by which you summed the products of arrays. The array SUM has been stable for as long as I've used it, going on 18 or 19 years. "MDW" wrote: I've inhereted a workbook where the previous owner used a complex {=SUM()} function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external -file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$1500 0))} -- Hmm...they have the Internet on COMPUTERS now! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{=SUM()} vs =SUMPRODUCT()
Thanks Duke. Your recollection is far better than mine. I first used Excel
in the late eighties, but I don't recall much <G Bob "Duke Carey" wrote in message ... Bob - I started using Excel in 1987. It was version 2, I think. Windows was not yet an OS, or at least the PC didn't boot into Windows. You started DOS, then started Windows, THEN you could run Excel. Excel was the only application I owned that ran on Windows. At that time Excel didn't have SUMPRODUCT. However, working in the investment banking field, and calculating weighted averages of all kinds, I was thrilled to be able to create array formulas that worked the way SUMPRODUCT does. A few years later (maybe Excel 5?) introduced SUMPRODUCT. I still use the array form of SUM() out of old habit more than I use SUMPRODUCT Old and not very valuable information, but that's my recollection. Duke "Bob Phillips" wrote: Maybe it wasn't used in that way, but I would be interested to hear when Excel didn't have SUMPRODUCT . -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Duke Carey" wrote in message ... Long ago, in a galaxy far away, Excel didn't have a SUMPRODUCT() function, and the array version of SUM() was the vehicle by which you summed the products of arrays. The array SUM has been stable for as long as I've used it, going on 18 or 19 years. "MDW" wrote: I've inhereted a workbook where the previous owner used a complex {=SUM()} function to approximate a SUMPRODUCT(). It currently is working fine, but I wonder if there are any advantages to changing the functions to be actual SUMPRODUCT. Does using SUM in this way take up more resources/become unstable, etc? I'm chasing down some gremlins in my Excel workbooks and this one popped out at me. For reference, the current function looks something like this: {=SUM(($A4='C:\[external-file.xls]sheet1'!$A$9:$A$15000)*(AX$1='C:\[external -file.xls]sheet1'!$C$9:$C$15000)*'C:\[external-file.xls]sheet1'!$F$9:$F$15 00 0))} -- Hmm...they have the Internet on COMPUTERS now! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AUTO SUM function returns 0.00 and formula displays =SUM() | New Users to Excel | |||
How do I add (=Sum) the same cell for multiple sheets (103) | Excel Worksheet Functions | |||
To data apearing in other sheets I can use =SUM(. How I can have . | Excel Worksheet Functions | |||
=SUM question | Excel Worksheet Functions | |||
=SUM Then insert word. | Excel Worksheet Functions |