adding numbers in a column with dates

P

piklelila

I have a column with dates and numbers. I want to add up the numbers at the
bottom of the column. For example:
A1 11/15
A2 33
A3 11/20
A4 45
A5 11/24
A6 66
etc...

I just want to add A2, A4, A6, etc.... Is there an easy way to do this? I
know that I can single out by =SUM(A2,A4,A6), but there are a lot more to add
in the column and I'll be doing it for each month. This way would be so
tedious and time consuming. Can anyone help?
 
D

Dave Peterson

Are the other values text ('11/15) or really numbers?

If they're really text, you could use: =sum(a1:a10)
Text values will be ignored.

If they're really numbers (maybe dates???), then this won't work for you.

I'd add another column that would be used as an indicator. Put an X in the row
if that value in column A should be summed.

=sumif(b:b,"x",a:a)

If those 11/15's are really dates, and the values aren't very big, you could use
a fromula like:

=sumif(a:a,"<"&date(2000,1,1))

Dates are just numbers to excel. And Jan 1, 2000 is 40177 (with 1904 as the
base year).
 
T

T. Valko

Are the numbers to sum *always* in the even numbered rows?

Are the numbers to sum *always* less than a certain value? For example, the
numbers to sum *will never* be greater than 100.

In Excel dates are really numbers that are formatted to look like dates.
Today's date is 12/30/2009. It *displays* as the date 12/30/2009 but its
true value is the number 40177. Trying to differentiate numbers from dates
can be complicated so we have to try to come up with some kind of logical
rule that distinguishes the numbers to sum from the number dates.
 
C

clr

Here's a couple ways, depending on the relativity of your dates to
numbers........
=SUMIF(A:A,"<"&9000)
=SUMIF(A:A,"<"&"12/1/2009")

Vaya con Dios,
Chuck, CABGx3
 
M

Mike H

Hi,

If the sample data you gave is typical then you want to sum even numbered
rows so try this

=SUMPRODUCT((1-MOD(ROW(A1:A10),2))*A1:A10)

Mike
 
T

trip_to_tokyo

It's not good design to mix dates and numbers in the same column: break them
out into 2 separate columns.

If my comments have helped please hit Yes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top