Problem using formula after formatting cells
 

I need to use dates and numbers in the same column and total only the numbers
using a formula. Once I changed the specific cells with the dates to "text"
so that they would not be included in the sum, I can no longer sum the
numbers. The problem doesn't go away if the cells are formated to "number",
"general" etc. The sum is always "0".
--
Stacey

Problem using formula after formatting cells
 

If you put a " ' " (without the quotes - meaning a single apostrophe)
in front of the dates -- all such cells should be eliminated from your SUM()
formula...

"Stacey" wrote:

I need to use dates and numbers in the same column and total only the numbers
using a formula. Once I changed the specific cells with the dates to "text"
so that they would not be included in the sum, I can no longer sum the
numbers. The problem doesn't go away if the cells are formated to "number",
"general" etc. The sum is always "0".
--
Stacey

Problem using formula after formatting cells
 

To excel, dates are just numbers nicely formatted.

Maybe you could use two columns--one for the date and one for the data.

Or maybe you could use two columns--one for the date/data and one as an
indicator. Then you could use =sumif() to add the numeric cells that have that
indicator.

=sumif(a1:a10,"x",b1:b10)

Or you could use that indicator column and then use a formula like:
=and(isnumber(b1),left(cell("format",b1),1)="D")

This will return true if B1 is a number (including a date) and the cell has been
formatted as a date.

Then you could use:
=sumif(a1:a10,false,b1:b10)

Be aware that that =cell("Format",b1) won't recalculate if you change the format
of B1. You'll have to force a recalculation (hit F9).




Stacey wrote:

I need to use dates and numbers in the same column and total only the numbers
using a formula. Once I changed the specific cells with the dates to "text"
so that they would not be included in the sum, I can no longer sum the
numbers. The problem doesn't go away if the cells are formated to "number",
"general" etc. The sum is always "0".
--
Stacey

--

Dave Peterson