Conditional format SUMPRODUCT

  • Thread starter Thread starter Arizona
  • Start date Start date
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?
 
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.
 
Conditional formatting can only change the format as specified but not the
cell value.
 
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
 
Back
Top