Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct returns value error when ref is formula
I am using sumproduct to build a report.
The referenced column has a formula in it. Sumproduct returns a value error message since there is a formula in the referenced cells. Example: Column A Month (entered) Column B Sales (entered) Column C Profit Percent (entered) Column D Profit dollars (results given by formula, column B * column C) Here is the problem =sumproduct((column a=January)*(column d)) This returns Value error because column D is a formula. If i enter the column D numbers it calculates fine =sumproduct((column a =January)*(columnb)) Works fine because column B is entered and not a formula Help?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct returns value error when ref is formula
I just tested this where col L value is the result of a formula. Perhaps
your Jan needs to be "Jan" =SUMPRODUCT((J1:J11="jan")*L1:L11) -- Don Guillett SalesAid Software "wx4usa" wrote in message ups.com... I am using sumproduct to build a report. The referenced column has a formula in it. Sumproduct returns a value error message since there is a formula in the referenced cells. Example: Column A Month (entered) Column B Sales (entered) Column C Profit Percent (entered) Column D Profit dollars (results given by formula, column B * column C) Here is the problem =sumproduct((column a=January)*(column d)) This returns Value error because column D is a formula. If i enter the column D numbers it calculates fine =sumproduct((column a =January)*(columnb)) Works fine because column B is entered and not a formula Help?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct returns value error when ref is formula
It shouldn't make any difference whether the referenced cells contain
formulas or input values, perhaps your column D contains #VALUE! error(s), try eliminating these or perhaps =sumproduct(--(column a ="January"),column b, column c) I assume column a contains text and not dates "wx4usa" wrote: I am using sumproduct to build a report. The referenced column has a formula in it. Sumproduct returns a value error message since there is a formula in the referenced cells. Example: Column A Month (entered) Column B Sales (entered) Column C Profit Percent (entered) Column D Profit dollars (results given by formula, column B * column C) Here is the problem =sumproduct((column a=January)*(column d)) This returns Value error because column D is a formula. If i enter the column D numbers it calculates fine =sumproduct((column a =January)*(columnb)) Works fine because column B is entered and not a formula Help?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct returns value error when ref is formula
The asterisk form of Sumproduct() will return a #VALUE! error if *ANY* cell
in the referenced total column (Column D) is *not* a number. This includes nulls ( "" ). So, if your formulas look something like this: =IF(B2*C2=0,"",B2*C2) You will get that #VALUE! error. Try these formulas, and see what happens: =SUMIF(A2:A100,"jan",D2:D100) =SUMPRODUCT(--(A2:A100="Jan"),D2:D100) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "wx4usa" wrote in message ups.com... I am using sumproduct to build a report. The referenced column has a formula in it. Sumproduct returns a value error message since there is a formula in the referenced cells. Example: Column A Month (entered) Column B Sales (entered) Column C Profit Percent (entered) Column D Profit dollars (results given by formula, column B * column C) Here is the problem =sumproduct((column a=January)*(column d)) This returns Value error because column D is a formula. If i enter the column D numbers it calculates fine =sumproduct((column a =January)*(columnb)) Works fine because column B is entered and not a formula Help?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Array formula returns blank in the cell where it is entered | Excel Worksheet Functions | |||
One Sumproduct Formula works - while other returns #VALUE!? | Excel Worksheet Functions | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Sumproduct: Formula is finicky | Excel Worksheet Functions |