sumif cell is not a date

  • Thread starter Thread starter scruffy323
  • Start date Start date
S

scruffy323

I am looking to sum entries in a column that are not a date.

So for this row I would like to get the sum 357

| Column:A | Column:B | Column:C | Column:D |
ROW:2 | 1/1/2008 | 234 | 1/2/2008 | 123 |

I want to do something like
sumif(A2:2,isnumber(currentcellsomehow),A2:2)

Does any one know how this is possible, I have been reading posts on
the web but can't find anything like this.
Steve
 
I am looking to sum entries in a column that are not a date.

So for this row I would like to get the sum 357

| Column:A | Column:B | Column:C | Column:D |
ROW:2 | 1/1/2008 | 234 | 1/2/2008 | 123 |

I want to do something like
sumif(A2:2,isnumber(currentcellsomehow),A2:2)

Does any one know how this is possible, I have been reading posts on
the web but can't find anything like this.
Steve
Click to expand...

http://www.dailydoseofexcel.com/archives/2005/07/16/multicolumn-sum-with-wildcard/#comment-30088
 
Well, for starters, dates are numbers. 1/1/2008 (Jan 1, 2008) is 39448 and
1/2/2008 (Jan 2, 2008) is 39449. You can manipulate those numbers as you
would any other. Excel maintains dates as numbers relative to January 0,
1900. January 1, 1900 is day 1, January 2, 1900 is day 2, etc. What you're
really wanting to do is to sum only certain columns in the row. Are you
attempting to sum every second column - e.g. B, D, F etc across the row and
if so, how far? If your just adding the two numbers in your example, then
=B2+D2 will work.


Tyro
 
Well, for starters, dates are numbers. 1/1/2008 (Jan 1, 2008) is 39448 and
1/2/2008 (Jan 2, 2008) is 39449. You can manipulate those numbers as you
would any other. Excel maintains dates as numbers relative to January 0,
1900. January 1, 1900 is day 1, January 2, 1900 is day 2, etc. What you're
really wanting to do is to sum only certain columns in the row. Are you
attempting to sum every second column - e.g. B, D, F etc across the row and
if so, how far? If your just adding the two numbers in your example, then
=B2+D2 will work.

Tyro
Click to expand...

Attempting to Exactly to sum every other column till the end of the
sheet.
 
The following array formula will add every second column in row 2 starting
with column B through column IV. After entering the formula, press
Ctrl+Shift+Enter to make it an array formula. In the formula bar, you will
see {=SUM(IF(MOD(COLUMN(A2:IV2),2)=0,A2:IV2,0))}. The braces indicate an
array formula and are put there when you press Ctrl+Shift+Enter. You do NOT
type the {}. If you are using Excel 2007, change the IV to XFD in the
formula.

=SUM(IF(MOD(COLUMN(A2:IV2),2)=0,A2:IV2,0))
 
I am looking to sum entries in a column that are not a date.

So for this row I would like to get the sum 357

| Column:A | Column:B | Column:C | Column:D |
ROW:2 | 1/1/2008 | 234 | 1/2/2008 | 123 |

I want to do something like
sumif(A2:2,isnumber(currentcellsomehow),A2:2)

Does any one know how this is possible, I have been reading posts on
the web but can't find anything like this.
Steve
Click to expand...

I don't think that, by using a formula, you can reliable differentiate between
a number and a date. This is because Excel stores dates as numbers.

However, if you wanted to add all the even columns in row 2 (e.g. B2, D2, F2,
etc), you could use this formula:

=SUMPRODUCT(2:2,--(MOD(COLUMN(2:2),2)=0))
--ron
 
You could do some kind of sanity check formula. For instance,

SUMPRODUCT(2:2,--(YEAR(2:2)>1945))

It would only be reliable for sample values you provided such as 123
and 234, but depending on your situation it may work. For example, if
you are pretty sure you won't have numbers between 32,874.00 and
40,179.00, then you can fairly safely use this kind of conditional sum
for dates between 1/1/1990 and 1/1/2010.
 
Thanks for the help

You could do some kind of sanity check formula. For instance,

SUMPRODUCT(2:2,--(YEAR(2:2)>1945))

It would only be reliable for sample values you provided such as 123
and 234, but depending on your situation it may work. For example, if
you are pretty sure you won't have numbers between 32,874.00 and
40,179.00, then you can fairly safely use this kind of conditional sum
for dates between 1/1/1990 and 1/1/2010.
Click to expand...
 
Back
Top