Sum column values that contains text &/or dates

[mikeburg]

Column D3 thru D43 contain a mixture of values & dates. For example:

D3 = 16,026.76
D4 = 11-18-05
D5 = blank
D6 = 6,855.34
D7= 11-03-05
D8 = blank
D9 = 5,270.00
D10 = 11-02-05

Need sum to equal 28,152.10 (total only values from entry or formulas)

Thanks so much. mikeburg

 

[Guest]

If your dates are text strings which say "11-18-05" for example, then the
solution below should work. If they're serial numbers formatted as you show
then, then the problem is trickier....

The following array formula work do the trick. You need to hold down the
control and shift keys while hitting Enter, in order to have excel recognize
it as an array.
{=SUM(IF(ISNUMBER(D343),D343))}

Don't know how well you know array formula.... don't actually type the {}
signs, those get inserted when you enter the formula holding down shift and
control.

 

[SteveG]

Mike,

There may be a single formula to do this but you could use a helper
column in column E to get the answer. I assume that all of your
numbers are formatted the same. Use the CELL function and SUMIF.

In Column E3:E10

=CELL("format",D3), drag down to E10

This identifies a numeric value using the thousands separator as ",2"
and dates as D4.

Then use this in the cell you want the calculation done in.

{=SUM(IF($E$3:$E$10=",2",$D$3:$D$10,0))}

Commit with Ctrl-Shift-Enter as it is an array formula.

Cheers,

Steve

 

[vezerid]

mikeburg,
the SUM() function will disregard text. It will not distinguish between
dates and numbers because dates are internally stored as numbers. Thus,
if you only want the sum of purely numeric values you will need to use
VBA and a helper column.

Function IsItANumber(x)
IsItANumber = isnumeric(x)
End Function

To define this function, Alt+F11 to go to the VBA editor, Insert|Module
and paste the function code above.

Back in Excel, in another column (say E3:E43), use =IsItANumber(D3) and
copy down to row 43. You can then use the following formula:
=SUMIF(E3:E43, TRUE, D343)

HTH
Kostis Vezerides

 

[Dave Peterson]

I would use a helper column of cells:

=IF(AND(ISNUMBER(D3),LEFT(CELL("format",D3),1)="D"),"",D3)
and drag down

Then sum that column.

Really, I'd separate my data into different columns. It could be a mess if
someone formats the whole column incorrectly.

 

[mikeburg]

Thanks for all your great help. mikeburg