Conditional formatting based on based on user input to sum particular cells

**MyJKU** · 02-25-2013, 05:19 PM

I'm trying to figure out a conditional statement that will add cells based on text inputs from another sheet. For example, if cell C1 contains the word "lighting", and cell D1 has the number 5 in it, I want the contents of D1 to be input into some arbitrary cell, lets say A1. However, if cell C1 contains the word "power" I want the contents of D1 to be placed in cell another cell lets say A2. I read somewhere that you cannot sum cells based on conditional statements, so I'm trying this around the world approach. If anyone has an easier way of doing this, please feel free to give me your advice.

**dredwolf** · 02-25-2013, 06:30 PM

Not sure exactly what you are asking here...summing, or deciding whether the value of d1 winds up in cell a1 or a2 depending on c1?
Also..CF only affects visual output of a cell, doesn't affect the contents of the cell, so it is probably not what you need

In a1 =IF(C1="lighting",D1,"")
In a2 =IF(C1="power",D1,"")

handles the latter, to sum a range (say D1:D100) based on contents of C1:C100

in a1 : =SUMIF($C$1:$C$100,"lighting",$D$1:$D$100)
in a2 : =SUMIF($C$1:$C$100,"power",$D$1:$D$100)

may work for the first..would depend on your data set-up

Hope this helps

**xlbiznes** · 02-25-2013, 06:30 PM

Hi,

You can sum numbers based on conditions.
use sumif() for a single condition or sumifs() for multiple conditions. This will solve your problem.

**MyJKU** · 02-25-2013, 06:39 PM

Originally Posted by dredwolf

Not sure exactly what you are asking here...summing, or deciding whether the value of d1 winds up in cell a1 or a2 depending on c1?
Also..CF only affects visual output of a cell, doesn't affect the contents of the cell, so it is probably not what you need

In a1 =IF(C1="lighting",D1,"")
In a2 =IF(C1="power",D1,"")

handles the latter, to sum a range (say D1:D100) based on contents of C1:C100

in a1 : =SUMIF($C$1:$C$100,"lighting",$D$1:$D$100)
in a2 : =SUMIF($C$1:$C$100,"power",$D$1:$D$100)

may work for the first..would depend on your data set-up

Hope this helps

Thanks, that helps a lot. One of my other problems was I was trying to assign a cell, instead of using just the cell identifier in the formula. Got hung up on programming language instead of excel language. Thanks, i got it figured out now.