Adding Column of mixed data omitting the dates

L

lpullen

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?
 
D

Dave Peterson

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.
 
A

Andrew Taylor

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
 
Top