Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Adding column data | Excel Worksheet Functions |