I am using the Autosum command to sum a range of cells. The cells with
conditional formating similar to the formula below are not included in the
autosum. Can you tell me why?

=IF(C17=12,"2.0",(IF(C17=9,"1.5",IF(C175,"1",IF (C170,"0.5","0")))))

Thanks,
LAM

Your values are text--not numbers.

=IF(C17=12,2,(IF(C17=9,1.5,IF(C175,1,IF(C170,0 .5,0)))))



LAM wrote:

I am using the Autosum command to sum a range of cells. The cells with
conditional formating similar to the formula below are not included in the
autosum. Can you tell me why?

=IF(C17=12,"2.0",(IF(C17=9,"1.5",IF(C175,"1",IF (C170,"0.5","0")))))

Thanks,
LAM

--

Dave Peterson

Hi,

1. You can't use that formula in Conditional Formatting for any useful
purpose.

2. Why store the numbers a text and then try to sum them?

3. If you must then change the autosum formua to something like this array:

=SUM(--A1:A20)

or this non-array

=SUMPRODUCT(--A1:A20)

Array - this means you need to press Shift+Ctrl+Enter to enter the formula.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LAM" wrote:

I am using the Autosum command to sum a range of cells. The cells with
conditional formating similar to the formula below are not included in the
autosum. Can you tell me why?

=IF(C17=12,"2.0",(IF(C17=9,"1.5",IF(C175,"1",IF (C170,"0.5","0")))))

Thanks,
LAM

Hi

Thanks for replying.

I am trying to assign office hours for instructors based on how long a class
is. Classes from 1-5 hours have 0.5 hours of office hours, classes 5 hours
have 1 office hour, etc. Then I want to sum the office hours in another
column.

I realized that I was generating text, but I wasn't sure if there was a way
to generate a value in a conditional formula. I guess not.

I am not familar with arrays, I will look into to them this morning.

thanks again.

"Shane Devenshire" wrote:

Hi,

1. You can't use that formula in Conditional Formatting for any useful
purpose.

2. Why store the numbers a text and then try to sum them?

3. If you must then change the autosum formua to something like this array:

=SUM(--A1:A20)

or this non-array

=SUMPRODUCT(--A1:A20)

Array - this means you need to press Shift+Ctrl+Enter to enter the formula.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LAM" wrote:

I am using the Autosum command to sum a range of cells. The cells with
conditional formating similar to the formula below are not included in the
autosum. Can you tell me why?

=IF(C17=12,"2.0",(IF(C17=9,"1.5",IF(C175,"1",IF (C170,"0.5","0")))))

Thanks,
LAM

THAT'S IT!!! I was sure that I tried that, but I guess not.

Thanks.

"Dave Peterson" wrote:

Your values are text--not numbers.

=IF(C17=12,2,(IF(C17=9,1.5,IF(C175,1,IF(C170,0 .5,0)))))



LAM wrote:

I am using the Autosum command to sum a range of cells. The cells with
conditional formating similar to the formula below are not included in the
autosum. Can you tell me why?

=IF(C17=12,"2.0",(IF(C17=9,"1.5",IF(C175,"1",IF (C170,"0.5","0")))))

Thanks,
LAM

--

Dave Peterson