# Adding Column of mixed data omitting the dates

1. ## 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?

2. ## Re: Adding Column of mixed data omitting the dates

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

3. ## Re: Adding Column of mixed data omitting the dates

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?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1