sumif cell is not a date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scruffy323 - 26 Jan 2008 21:40 GMT

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

Reply to this Message

scruffy323 - 26 Jan 2008 21:46 GMT

> I am looking to sum entries in a column that are not a date.
>
[quoted text clipped - 9 lines]
> the web but can't find anything like this.
> Steve

http://www.dailydoseofexcel.com/archives/2005/07/16/multicolumn-sum-with-wildcar
d/#comment-30088

Reply to this Message

Tyro - 26 Jan 2008 22:23 GMT

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

>> I am looking to sum entries in a column that are not a date.
>>
[quoted text clipped - 11 lines]
>
> http://www.dailydoseofexcel.com/archives/2005/07/16/multicolumn-sum-with-wildcar
d/#comment-30088

Reply to this Message

scruffy323 - 26 Jan 2008 23:08 GMT

> 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
[quoted text clipped - 22 lines]
>
> >http://www.dailydoseofexcel.com/archives/2005/07/16/multicolumn-sum-w...

Attempting to Exactly to sum every other column till the end of the
sheet.

Reply to this Message

Tyro - 27 Jan 2008 01:47 GMT

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))

>> Well, for starters, dates are numbers. 1/1/2008 (Jan 1, 2008) is 39448
>> and
[quoted text clipped - 28 lines]
> Attempting to Exactly to sum every other column till the end of the
> sheet.

Reply to this Message

Tyro - 27 Jan 2008 01:54 GMT

To clarify: "After entering the formula" should read "After typing in the
formula"

Tyro

> The following array formula will add every second column in row 2 starting
> with column B through column IV. After entering the formula, press
[quoted text clipped - 5 lines]
>
> =SUM(IF(MOD(COLUMN(A2:IV2),2)=0,A2:IV2,0))

Reply to this Message

Ron Rosenfeld - 27 Jan 2008 02:08 GMT

>I am looking to sum entries in a column that are not a date.
>
[quoted text clipped - 9 lines]
>the web but can't find anything like this.
>Steve

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

Reply to this Message

ilia - 27 Jan 2008 20:52 GMT

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.

> >I am looking to sum entries in a column that are not a date.
>
[quoted text clipped - 18 lines]
> =SUMPRODUCT(2:2,--(MOD(COLUMN(2:2),2)=0))
> --ron

Reply to this Message

scruffy323 - 28 Jan 2008 08:02 GMT

Thanks for the help

> You could do some kind of sanity check formula. For instance,
>
[quoted text clipped - 28 lines]
> > =SUMPRODUCT(2:2,--(MOD(COLUMN(2:2),2)=0))
> > --ron

Reply to this Message