SUM and IF results

[Guest]

I want to SUM a horizontal row of cells, of which one contains the IF function:
=IF(C2<2,"50",IF(C2=2,"25",IF(C2>3,"0")))

I want the results of the IF function, 50, 25, or 0, to be included in the
SUM.
Any tips?

 

[Arvi Laanemets]

Hi

Replace your formula with
=IF(C2<2,50,IF(C2=2,25,IF(C2>3,0)))

But what about 3>=C2>2

Btw. you can do without any IF's (slightly different conditions in my
example here)

=50-(C2=2)-(C2>2)

 

[T. Valko]

=IF(C2<2,"50",IF(C2=2,"25",IF(C2>3,"0")))

At the very least remove the quotes from around the numbers. Enclosing
numbers in quotes makes them TEXT and a SUM function ignores text.

=IF(C2<2,50,IF(C2=2,25,IF(C2>3,0)))

Now, about your logic in the formula...

If C2 = 3 the result will be FALSE. If C2 is empty the result will be 50.

So, I'd write the formula like this:

=IF(ISNUMBER(C2),IF(C2<2,50,IF(C2=2,25,0)),0)

Now you can include that cell in your SUM function.

Biff

 

[T. Valko]

=50-(C2=2)-(C2>2)

Think you're missing some stuff there:

=50-(C2=2)*25-(C2>2)*50

And to account for an empty cell:

=(50-(C2=2)*25-(C2>2)*50)*(C2<>"")

Biff

 

[Guest]

Arvi!
Thanks, I love the simple solutions.
David

Hi

Replace your formula with
=IF(C2<2,50,IF(C2=2,25,IF(C2>3,0)))

But what about 3>=C2>2

Btw. you can do without any IF's (slightly different conditions in my
example here)

=50-(C2=2)-(C2>2)

 

[Guest]

T!
Thanks, I love the simple answers.
David

At the very least remove the quotes from around the numbers. Enclosing
numbers in quotes makes them TEXT and a SUM function ignores text.

=IF(C2<2,50,IF(C2=2,25,IF(C2>3,0)))

Now, about your logic in the formula...

If C2 = 3 the result will be FALSE. If C2 is empty the result will be 50.

So, I'd write the formula like this:

=IF(ISNUMBER(C2),IF(C2<2,50,IF(C2=2,25,0)),0)

Now you can include that cell in your SUM function.

Biff