Exclude dates from a sum formula
 

I have a spreadsheet, several, actually, that have columns containing both
numbers and dates. I want to add accross the colums, but exclude the dates.
I didn't realize that it was adding the numeric value of the dates in my end
formula, and I don't want to have to do the +c12+e12+g12, etc. Any thoughts
on making this easier? I tried storing the dates as text, and that seems to
be OK but the stupid error checking is driving me NUTS! I would like to have
them formatted as dates, just not included.

Thanks!

Exclude dates from a sum formula
 

Sir,

Jan 1, 2008 is stored as a number, 39,448. If your other numbers (the ones
you want to sum) are always smaller than that you could use
=sumproduct(--(a1:x1<39448),(a1:x1)). Other than that, I don't know...

HTH,
Jim

"SirMatthew" wrote:

I have a spreadsheet, several, actually, that have columns containing both
numbers and dates. I want to add accross the colums, but exclude the dates.
I didn't realize that it was adding the numeric value of the dates in my end
formula, and I don't want to have to do the +c12+e12+g12, etc. Any thoughts
on making this easier? I tried storing the dates as text, and that seems to
be OK but the stupid error checking is driving me NUTS! I would like to have
them formatted as dates, just not included.

Thanks!

Exclude dates from a sum formula
 

I would insert a row and use it as an indicator--say a new row 1. Then put some
indicator in row 1 of the columns that should be added--say the word "Add".

Then I could use =sumif()

=sumif($1:$1,"add",3:3)



SirMatthew wrote:

I have a spreadsheet, several, actually, that have columns containing both
numbers and dates. I want to add accross the colums, but exclude the dates.
I didn't realize that it was adding the numeric value of the dates in my end
formula, and I don't want to have to do the +c12+e12+g12, etc. Any thoughts
on making this easier? I tried storing the dates as text, and that seems to
be OK but the stupid error checking is driving me NUTS! I would like to have
them formatted as dates, just not included.

Thanks!

--

Dave Peterson

Exclude dates from a sum formula
 

I don't want to have to do the +c12+e12+g12, etc.

This formula will sum every other cell starting at cell C12 to cell K12:

=SUMPRODUCT(--(MOD(COLUMN(C12:K12),2)=1),C12:K12)

This formula is the same as the above but will account for new column
insertions before the range:

=SUMPRODUCT(--(MOD(COLUMN(C12:K12)-COLUMN(C12),2)=0),C12:K12)

If you will *never* insert new columns before the range use the shorter
formula.


--
Biff
Microsoft Excel MVP


"SirMatthew" wrote in message
...
I have a spreadsheet, several, actually, that have columns containing both
numbers and dates. I want to add accross the colums, but exclude the
dates.
I didn't realize that it was adding the numeric value of the dates in my
end
formula, and I don't want to have to do the +c12+e12+g12, etc. Any
thoughts
on making this easier? I tried storing the dates as text, and that seems
to
be OK but the stupid error checking is driving me NUTS! I would like to
have
them formatted as dates, just not included.

Thanks!