Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Issue with sumproduct
Hello from Steved
Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE! My objective is to sum the extra 3 columns. What is required please to have it working. =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109) Thankyou. |
#2
|
|||
|
|||
I believe you can only have the function operate on one column, so add
together 4 sumproducts (one for each of you columns). "Steved" wrote: Hello from Steved Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE! My objective is to sum the extra 3 columns. What is required please to have it working. =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109) Thankyou. |
#3
|
|||
|
|||
This works for me:
=SUMPRODUCT(($A$4:$A$109=1)*($B$4:$B$109="P")*D4:G 109) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steved" wrote in message ... Hello from Steved Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE! My objective is to sum the extra 3 columns. What is required please to have it working. =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109) Thankyou. |
#4
|
|||
|
|||
Thankyou Duane
"duane" wrote: I believe you can only have the function operate on one column, so add together 4 sumproducts (one for each of you columns). "Steved" wrote: Hello from Steved Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE! My objective is to sum the extra 3 columns. What is required please to have it working. =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109) Thankyou. |
#5
|
|||
|
|||
Ragdyer Thankyou
"Ragdyer" wrote: This works for me: =SUMPRODUCT(($A$4:$A$109=1)*($B$4:$B$109="P")*D4:G 109) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steved" wrote in message ... Hello from Steved Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE! My objective is to sum the extra 3 columns. What is required please to have it working. =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109) Thankyou. |
#6
|
|||
|
|||
Create an additional column, say H, with from H4 on:
=SUM(D4:G4) and invoke: =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!H4:H109) Steved wrote: Hello from Steved Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE! My objective is to sum the extra 3 columns. What is required please to have it working. =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109) Thankyou. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#7
|
|||
|
|||
Hello Aladin from Steved
Thankyou I personally would not do it as I believe the less formulas the better. Just speaking for myself. Cheers. "Aladin Akyurek" wrote: Create an additional column, say H, with from H4 on: =SUM(D4:G4) and invoke: =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!H4:H109) Steved wrote: Hello from Steved Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE! My objective is to sum the extra 3 columns. What is required please to have it working. =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109) Thankyou. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#8
|
|||
|
|||
Steved wrote:
Hello Aladin from Steved Thankyou I personally would not do it as I believe the less formulas the better. Just speaking for myself. [...] Really? |
#9
|
|||
|
|||
"Aladin Akyurek" wrote in message
... Steved wrote: Hello Aladin from Steved Thankyou I personally would not do it as I believe the less formulas the better. Just speaking for myself. [...] Really? I am perfectly sure that Aladin does not need me to talk for him and probably his reply says much more than I am about to, but the number of calculations in a formula is not always apparent at face value. I stand to be corrected in this and if I am shot down in flames it will only serve to increase my understanding of XL If we take the formula that Regdyer gave (cut down to make it manageable) =SUMPRODUCT(($A$4:$A$6=1)*($B$4:$B$6="P")*D4:G6) as an example it, it looks like it has 3 evaluations in Column A + 3 evaluations in Column B+ 4 * 3 calculations in Columns G to G making a total of 18 calculations. However, if we highlight (($A$4:$A$6=1) and press f9 we see {True;True;True;True} as does ($B$4:$B$6="P"). D4:G6 produces {ValueD4,ValueE4,ValueF4,ValueG4; ValueD5,ValueE5,ValueF5,ValueG5; ValueD6,ValueE6,ValueF6,ValueG6} So when this is evaluated we get: 1*1*ValueD4 , 1*1*ValueE4 , 1*1*ValueF4 , 1*1*ValueG4 ; 1*1*ValueD5, ......but wait a minute where did those 1*1's come from? we only had one set of TRUE's in each bracket and we have already used them. Surely Columns A & B have to be evaluated again to provide the extra sets of TRUE's. If so then the true total of calculations is: 3 * 4 evaluations of Column A + 3 * 4 evaluations of Column B + 3 * 4 calculations in Columns D to G making a total of 36 calculations. Aladin's suggestion uses 3 SUM calculations in Column H + 3 evaluations in Column A + 3 evaluations in Column B + 3 calculations of the SUMs in Column H making a total of 12 calculations - 1/3 of the original number! So which solutuon has the fewer calculations? -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT issue | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |