Conditional format SUMPRODUCT

A

Arizona

I am trying to create a simple conditional format in a cell to multiply the
cell value if it is greater than 0 by a value in another column.

I have tried =IF(D1>0,SUMPRODUCT(D1,O1),"") and =SUMIF(C1,">0",C1)*O1. The
conditional format (colour) changes in the cell but, the cell value does not
multiply by the $112 which is what is in cell O1.

How else could I write this?
 
F

Fred Smith

You're making your life way too complicated. Try the following formulas:
=if(d1>0,d1*o1,"")
=if(c1>0,o1,0)

Remember, the first formula will multiply by O1 (your $112) *only if* d1 is
greater than 0.

Regards,
Fred.
 
J

Jacob Skaria

Conditional formatting can only change the format as specified but not the
cell value.
 
T

Tom Hutchins

Conditional formatting can't change the value of a cell; it just changes the
way the cell appears. You don't need a formula to highlight a cell which has
a value greater than zero. Select the target cell (C1), then in the
Conditional Formatting dialog, select "Cell Value Is", "greater than", and
enter 0. Choose the formatting you want to apply, then click OK.

If C1 is a calculated result (contains a formula), wrap an IF function
around the formula in C1 to effect the conditional multiplication:

=If((A1-(B1/B2))>0,C1*O1,C1)

In this example the formula in C1 had been =A1-(B1/B2).

If you mean you want to manually enter a number in C1 and have it instantly
multiply itself by the number in O1, it's not impossible but it is probably a
bad idea. I suggest you test the value of C1 and do the multiplication in
another cell:

=If(C1>0,C1*O1,"")

Hope this helps,

Hutch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top