Adding Column of mixed data omitting the dates

Hello all,

We have an Excel sheet with a column that contains both dates and
numbers.

We need to sum the numbers, but omit the dates. The data is in random
sequence.

Any ideas?

One way would be to insert a column and put an indicator in that column.

Then you could use something like:
=sumif(b1:b10,"x",a1:a10)

But if you do that work, it might be just simpler to separate your dates from
the numbers to be summed.


lpullen wrote:
>
> Hello all,
>
> We have an Excel sheet with a column that contains both dates and
> numbers.
>
> We need to sum the numbers, but omit the dates. The data is in random
> sequence.
>
> Any ideas?

--

Dave Peterson

As you probably realise, the difficulty with this is that dates _are_
numbers: they just happen to be formatted in a special way. I think
you can achieve what you want by using the CELL() function, which
can give you information about the format of a cell. Assuming your
dates & numbers are in A1:A100, and that column B is free, enter
the formula =LEFT(CELL("format",A1),1) in B1 and copy it down to all of
B1:B100. Then the formula =SUMIF(B1:B100,"<>D",A1:A100) gives
you the sum of the numbers.

You can hide column B if it spoils the layout.

hth
Andrew Taylor


lpullen wrote:
> Hello all,
>
> We have an Excel sheet with a column that contains both dates and
> numbers.
>
> We need to sum the numbers, but omit the dates. The data is in random
> sequence.
>
> Any ideas?