#value! error, why? (Stumped)

[mpartyka]

Cell W12 contains this formula:

=SUM(G12+H12+I12+J12+K12+M12+O12+Q12+S12+U12)

I cannot change the referenced cells (ie G12 etc, or make it G12:U12)

How can I make cell W12 blank even if the cells in the formula are blank, filled in, or partially filled in? I hope I explained myself well enough?

 

[Todd Bardoni]

+ is for summing -or- ORing. * is for multiplication -or- ANDing.

 

[mpartyka]

I got that part (mostly anyway). I have used combinations like +or and *or
by trial and error and they both do seperate things (determining when to use each one is difficult, to say the least). I suspect I am curious if there are "rules" for combining functions like that?

 

[Todd Bardoni]

I've never seen +or/*or.

Also, there are no rules, its all based on need.

Let's say:-

I want to count the number of blue pants or blue shirts:-

blue pants
red pants
blue shirt
red shirt

My answer here would be 2 - 1 blue pants, 1 blue shirt=2

This could be represented with:- =SUMPRODUCT(--(rng1="Blue"),--(rng2="Pants")+--(rng2="Shirt"))

But also in that statement is an AND. The AND part is searching for the color Blue. So, to read it out loud would be:- I want my criteria to equal the color Blue AND I want it to equal Shirts OR Pants. (The comma after Blue represents AND much in the same way an asterisk does.)

Does that make sense?

 

[mpartyka]

Yes that makes sense, thank you.