Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E. Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with a
date, but I've been unable to figure out how to sum everything without a
date. Replacing the "" with "<" returns zero (0).

On Mon, 24 Nov 2008 13:15:01 -0800, AnotherNewGuy
wrote:

Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E. Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with a
date, but I've been unable to figure out how to sum everything without a
date. Replacing the "" with "<" returns zero (0).

Without regard to what might be in E:E if there is no date, and since you write
that your current SUMIF formula works correctly, you could use this:

=sum(c:c)-SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C )

--ron

Try this:
=SUMPRODUCT(--(A2:A200),--(E2:E20=""))


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott


"AnotherNewGuy" wrote:

Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E. Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with a
date, but I've been unable to figure out how to sum everything without a
date. Replacing the "" with "<" returns zero (0).

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E. Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with a
date, but I've been unable to figure out how to sum everything without a
date. Replacing the "" with "<" returns zero (0).

I seem to keep having these "Duh!" moments today. Thx. Too easy.

"Rick Rothstein" wrote:

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E. Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with a
date, but I've been unable to figure out how to sum everything without a
date. Replacing the "" with "<" returns zero (0).

Hi,

Just one caveat, if column E contains anything this formula SUMS column C,
ever if the entry in column E is text, a non numeric date or a spacebar, or
maybe more importantly if there is a formula in column E returning "".

Cheers,
Shane Devenshire

"AnotherNewGuy" wrote:

I seem to keep having these "Duh!" moments today. Thx. Too easy.

"Rick Rothstein" wrote:

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E. Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with a
date, but I've been unable to figure out how to sum everything without a
date. Replacing the "" with "<" returns zero (0).

Are you sure about that last one? My tests in XL2003 did not add the numbers
from Column C when a "" was the result of a formula in Column E.

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

Just one caveat, if column E contains anything this formula SUMS column C,
ever if the entry in column E is text, a non numeric date or a spacebar,
or
maybe more importantly if there is a formula in column E returning "".

Cheers,
Shane Devenshire

"AnotherNewGuy" wrote:

I seem to keep having these "Duh!" moments today. Thx. Too easy.

"Rick Rothstein" wrote:

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in
message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E.
Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with
a
date, but I've been unable to figure out how to sum everything
without a
date. Replacing the "" with "<" returns zero (0).

Someone could gum up the works by tapping the spacebar to "erase" an
incorrect date. A formula returning "" in E doesn't hurt, though. At least
my experiment with =trim(" ") worked.

Is there a way to use a function -- like trim() in the second parameter?


"Shane Devenshire" wrote:

Hi,

Just one caveat, if column E contains anything this formula SUMS column C,
ever if the entry in column E is text, a non numeric date or a spacebar, or
maybe more importantly if there is a formula in column E returning "".

Cheers,
Shane Devenshire

"AnotherNewGuy" wrote:

I seem to keep having these "Duh!" moments today. Thx. Too easy.

"Rick Rothstein" wrote:

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E. Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything with a
date, but I've been unable to figure out how to sum everything without a
date. Replacing the "" with "<" returns zero (0).

Since Column E will only contain dates, blanks or the possible occasional
space (that is, the only numbers in the column will be dates), you could use
this formula...

=SUMPRODUCT(NOT(ISNUMBER(E1:E10000))*C1:C10000)

Note that SUMPRODUCT cannot specify an entire column using C:C or E:E
syntax, so you have to specify a specific range. It is more efficient to
specify the minimum range that will ever be needed (which is rarely the
entire column). In my example, I assumed Row 10000 was the highest row
number that would ever contain data... change both 10000s (they must both be
the same row number) to whatever is actually the maximum anticipated row for
your data. In the above format, additional conditions can be easily imposed
if necessary.

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in message
...
Someone could gum up the works by tapping the spacebar to "erase" an
incorrect date. A formula returning "" in E doesn't hurt, though. At
least
my experiment with =trim(" ") worked.

Is there a way to use a function -- like trim() in the second parameter?


"Shane Devenshire" wrote:

Hi,

Just one caveat, if column E contains anything this formula SUMS column
C,
ever if the entry in column E is text, a non numeric date or a spacebar,
or
maybe more importantly if there is a formula in column E returning "".

Cheers,
Shane Devenshire

"AnotherNewGuy" wrote:

I seem to keep having these "Duh!" moments today. Thx. Too easy.

"Rick Rothstein" wrote:

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in
message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E.
Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything
with a
date, but I've been unable to figure out how to sum everything
without a
date. Replacing the "" with "<" returns zero (0).

Thanks Rick. This will work nicely for me.

As I understand it, there's really not a good way to use functions with
required parameters in sumif() -- today() would be fine, but is isnumber() is
not.



"Rick Rothstein" wrote:

Since Column E will only contain dates, blanks or the possible occasional
space (that is, the only numbers in the column will be dates), you could use
this formula...

=SUMPRODUCT(NOT(ISNUMBER(E1:E10000))*C1:C10000)

Note that SUMPRODUCT cannot specify an entire column using C:C or E:E
syntax, so you have to specify a specific range. It is more efficient to
specify the minimum range that will ever be needed (which is rarely the
entire column). In my example, I assumed Row 10000 was the highest row
number that would ever contain data... change both 10000s (they must both be
the same row number) to whatever is actually the maximum anticipated row for
your data. In the above format, additional conditions can be easily imposed
if necessary.

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in message
...
Someone could gum up the works by tapping the spacebar to "erase" an
incorrect date. A formula returning "" in E doesn't hurt, though. At
least
my experiment with =trim(" ") worked.

Is there a way to use a function -- like trim() in the second parameter?


"Shane Devenshire" wrote:

Hi,

Just one caveat, if column E contains anything this formula SUMS column
C,
ever if the entry in column E is text, a non numeric date or a spacebar,
or
maybe more importantly if there is a formula in column E returning "".

Cheers,
Shane Devenshire

"AnotherNewGuy" wrote:

I seem to keep having these "Duh!" moments today. Thx. Too easy.

"Rick Rothstein" wrote:

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in
message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E.
Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything
with a
date, but I've been unable to figure out how to sum everything
without a
date. Replacing the "" with "<" returns zero (0).

SUMIF and COUNTIF will allow simple relational expressions (where a single
value is being tested)... complex function calls (one's with arguments) are
not allowed.

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in message
...
Thanks Rick. This will work nicely for me.

As I understand it, there's really not a good way to use functions with
required parameters in sumif() -- today() would be fine, but is isnumber()
is
not.



"Rick Rothstein" wrote:

Since Column E will only contain dates, blanks or the possible occasional
space (that is, the only numbers in the column will be dates), you could
use
this formula...

=SUMPRODUCT(NOT(ISNUMBER(E1:E10000))*C1:C10000)

Note that SUMPRODUCT cannot specify an entire column using C:C or E:E
syntax, so you have to specify a specific range. It is more efficient to
specify the minimum range that will ever be needed (which is rarely the
entire column). In my example, I assumed Row 10000 was the highest row
number that would ever contain data... change both 10000s (they must both
be
the same row number) to whatever is actually the maximum anticipated row
for
your data. In the above format, additional conditions can be easily
imposed
if necessary.

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in
message
...
Someone could gum up the works by tapping the spacebar to "erase" an
incorrect date. A formula returning "" in E doesn't hurt, though. At
least
my experiment with =trim(" ") worked.

Is there a way to use a function -- like trim() in the second
parameter?


"Shane Devenshire" wrote:

Hi,

Just one caveat, if column E contains anything this formula SUMS
column
C,
ever if the entry in column E is text, a non numeric date or a
spacebar,
or
maybe more importantly if there is a formula in column E returning "".

Cheers,
Shane Devenshire

"AnotherNewGuy" wrote:

I seem to keep having these "Duh!" moments today. Thx. Too easy.

"Rick Rothstein" wrote:

What about this?

=SUMIF(E:E,"",C:C)

--
Rick (MVP - Excel)


"AnotherNewGuy" wrote in
message
...
Column C is a column of number
Column E is a column of dates

I want to sum every number in C with no corresponding date in E.
Seems
simple, but . . . .

=SUMIF(E:E,"" & DATEVALUE("1/1/1980"),C:C ) will sum everything
with a
date, but I've been unable to figure out how to sum everything
without a
date. Replacing the "" with "<" returns zero (0).