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??

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??

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??

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??