Excel Formula
 

Row A contains numbers either 1, 1.5, 2 or 3
At the end of the row is a formula to add up the numbers but I would like a
formula that adds them up but if there is 1.5 in the cell I want the formula
to think of it as 1 for example.

instead of 1, 1, 1, 1.5 = 4.5
I would like it to total 4

Is this possible

Excel Formula
 

=INT(SUM(A1:D1))

"grobertson" wrote:

Row A contains numbers either 1, 1.5, 2 or 3
At the end of the row is a formula to add up the numbers but I would like a
formula that adds them up but if there is 1.5 in the cell I want the formula
to think of it as 1 for example.

instead of 1, 1, 1, 1.5 = 4.5
I would like it to total 4

Is this possible

Excel Formula
 

It depends on when you want the transformation to occur...

Example:
If A1:D1 contains 1.5 1.5 1.5 1.5

This formula =INT(SUM(A1:D1)) returns 6

but
This formula =SUMPRODUCT(INT(A1:D1)) returns 4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"grobertson" wrote:

Row A contains numbers either 1, 1.5, 2 or 3
At the end of the row is a formula to add up the numbers but I would like a
formula that adds them up but if there is 1.5 in the cell I want the formula
to think of it as 1 for example.

instead of 1, 1, 1, 1.5 = 4.5
I would like it to total 4

Is this possible

Excel Formula
 

another way would be to use an array formula
=sum(if(A1:Z1=1.5,1,A1:Z1))
enter as <control-shift-enter

"grobertson" wrote:

Row A contains numbers either 1, 1.5, 2 or 3
At the end of the row is a formula to add up the numbers but I would like a
formula that adds them up but if there is 1.5 in the cell I want the formula
to think of it as 1 for example.

instead of 1, 1, 1, 1.5 = 4.5
I would like it to total 4

Is this possible

Excel Formula
 

If you want to add elements of Col_C where corresponding cells in Col_A are
NOT blank then your syntax is a bit off.

Try this:
=SUMIF($A:$A,"<"&"",$C:$C)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"grobertson" wrote:

Row A contains numbers either 1, 1.5, 2 or 3
At the end of the row is a formula to add up the numbers but I would like a
formula that adds them up but if there is 1.5 in the cell I want the formula
to think of it as 1 for example.

instead of 1, 1, 1, 1.5 = 4.5
I would like it to total 4

Is this possible

Excel Formula
 

Well, that post sure ended up in the wrong bucket!
I suspect a hardware problem......probably a loose nut in front of my
keyboard.



"Ron Coderre" wrote in message
...
If you want to add elements of Col_C where corresponding cells in Col_A
are
NOT blank then your syntax is a bit off.

Try this:
=SUMIF($A:$A,"<"&"",$C:$C)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"grobertson" wrote:

Row A contains numbers either 1, 1.5, 2 or 3
At the end of the row is a formula to add up the numbers but I would like
a
formula that adds them up but if there is 1.5 in the cell I want the
formula
to think of it as 1 for example.

instead of 1, 1, 1, 1.5 = 4.5
I would like it to total 4

Is this possible