Sum numbers in a column , exclude date value

[annalee]

Hello All

I would like to sum all figures in a column using SUM function but it tend to sum the dates in as well. Any advices to exclude the dates? Tried using ISNUMBER() but realized dates are actually recognized as number value.

Note: Sum entire column due to uncertain number of rows to be generated in future.

Thanks in advance!

 

[steve the fish]

What are the date ranges? What is the upper value of the data you need to sum? It may be possible to exclude the dates by excluding certain values from the sum. The day today for example is 42114. Or better still remove the dates from the sum range.

 

[annalee]

What are the date ranges? What is the upper value of the data you need to sum? It may be possible to exclude the dates by excluding certain values from the sum. The day today for example is 42114. Or better still remove the dates from the sum range.

Hello Steve the Fish

There is only one date value per column. Is there anyway to sum from second row onwards? Below is a sample.
[TABLE="width: 1043"]
<tbody>[TR]
[TD]Jan-15
[/TD]
[TD]Feb-15
[/TD]
[TD]Mar-15
[/TD]
[TD]Apr-15
[/TD]
[TD]May-15
[/TD]
[TD]Jun-15
[/TD]
[TD]Jul-15
[/TD]
[TD]Aug-15
[/TD]
[TD]Sep-15
[/TD]
[TD]Oct-15
[/TD]
[TD]Nov-15
[/TD]
[TD]Dec-15
[/TD]
[TD]Jan-16
[/TD]
[TD]Feb-16
[/TD]
[TD]Mar-16
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]26
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]26
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]27
[/TD]
[TD]26
[/TD]
[TD]24
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
If it is impossible, I will edit manually if the the inputs in the column exceeded the sum range.

 

[Harishiyer]

If u have values in Range A

say in A1 = 45
A2 = 02/15/2015

in B1 Cell enter this formula

=IFERROR(IF(TEXT(IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT,A1 < EOFY)),0)="FALSE",VALUE(A1),IF(TEXT(IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT,A1 < EOFY)),0)="#NAME?","")),VALUE("0"))

Drag it till the end

in C1 cell enter this formula

=SUM(B:B)

Done.

Thanks,
regards Harish

 

[steve the fish]

Maybe

=SUM(A2:A100)

 

[Eric Golf]

Hi,

Alternatively subtract the Date value after summing the column e.g.

=SUM(A:A)-A1

Hope this helps,

Eric.

 

[annalee]

Hi,

Alternatively subtract the Date value after summing the column e.g.

=SUM(A:A)-A1

Hope this helps,

Eric.

Thanks All for your help and suggestions!

Thanks Eric for your short and sweet solution. I wonder why I never thought of it. And of course, it worked. LOL