Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct producing #VALUE!
hi all,
i have the following formula =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5)) in effect this is a simple sumif, the reason i'm using sumproduct is that i am going to expand it to more than one criteria. my problem lies in the range i am summing (TABLE1!N2:N5), contains text and error values (first example in cell j5, if i shrink the range to only look down to cell j4, it works) i have tried using '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results PC'!B$13)*ISNUMBER((TABLE1!N2:N5))) but this gives me the count of number cells in the range, not the summed range needed. have googled this for wuite some time and have come up with no solution - over to the group. Thanks in advance. |
#2
|
|||
|
|||
If TABLE1!N2:N5 does not house any error values...
Invoke SumProduct with the comma syntax: =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)+0,TABLE1!N2:N5) Otherwise, you have to switch to: =SUM(IF(TABLE1!J2:J5='Summary Results PC'!B13,IF(ISNUMBER(TABLE1!N2:N5),TABLE1!N2:N5))) which must be confirmed with control+shift+enter. philcud wrote: hi all, i have the following formula =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5)) in effect this is a simple sumif, the reason i'm using sumproduct is that i am going to expand it to more than one criteria. my problem lies in the range i am summing (TABLE1!N2:N5), contains text and error values (first example in cell j5, if i shrink the range to only look down to cell j4, it works) i have tried using '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results PC'!B$13)*ISNUMBER((TABLE1!N2:N5))) but this gives me the count of number cells in the range, not the summed range needed. have googled this for wuite some time and have come up with no solution - over to the group. Thanks in advance. -- [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. |
#3
|
|||
|
|||
Perhaps try also:
=SUMPRODUCT((Table1!J2:J5='Summary Results PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "philcud" wrote in message ups.com... hi all, i have the following formula =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5)) in effect this is a simple sumif, the reason i'm using sumproduct is that i am going to expand it to more than one criteria. my problem lies in the range i am summing (TABLE1!N2:N5), contains text and error values (first example in cell j5, if i shrink the range to only look down to cell j4, it works) i have tried using '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results PC'!B$13)*ISNUMBER((TABLE1!N2:N5))) but this gives me the count of number cells in the range, not the summed range needed. have googled this for wuite some time and have come up with no solution - over to the group. Thanks in advance. |
#4
|
|||
|
|||
That's okay for text Max, but won't work with an error result.
Bob "Max" wrote in message ... Perhaps try also: =SUMPRODUCT((Table1!J2:J5='Summary Results PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "philcud" wrote in message ups.com... hi all, i have the following formula =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5)) in effect this is a simple sumif, the reason i'm using sumproduct is that i am going to expand it to more than one criteria. my problem lies in the range i am summing (TABLE1!N2:N5), contains text and error values (first example in cell j5, if i shrink the range to only look down to cell j4, it works) i have tried using '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results PC'!B$13)*ISNUMBER((TABLE1!N2:N5))) but this gives me the count of number cells in the range, not the summed range needed. have googled this for wuite some time and have come up with no solution - over to the group. Thanks in advance. |
#5
|
|||
|
|||
"Bob Phillips" wrote
That's okay for text Max, but won't work with an error result. Yes, you're right. Thanks. Should have tested more thoroughly Looks like we've to go the array route (similar to Aladin's 2nd formula): =SUM(IF((Table1!J2:J5='Summary Results PC'!B$13)*(ISNUMBER((Table1!N2:N5))),Table1!N2:N5) ) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
many thanks for all your help, in the end i used:
=SUMPRODUCT((TABLE1!$J$2:$J$2000=$B$45)*(TABLE1!$V $2:$V$2000="n")*(ISNUMBER((TABLE1!$N$2:$N$2000))), TABLE1!$N$2:$N$2000) (note i have increased the range sizes and criteria) as it turned out, what i thought were na errors, was actually text inputted by the user. Thanks again |
#7
|
|||
|
|||
Glad to hear that !
Thanks for feeding back to us .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | 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 |