Sumif() with dates

[AnotherNewGuy]

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

 

[Ron Rosenfeld]

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

 

[porter444]

Try this:
=SUMPRODUCT(--(A2:A20>0),--(E2:E20=""))


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

ROCK ON!,

Scott

 

[Rick Rothstein]

What about this?

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

 

[AnotherNewGuy]

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

 

[Shane Devenshire]

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

 

[Rick Rothstein]

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.

 

[AnotherNewGuy]

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?

 

[Rick Rothstein]

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.

 

[AnotherNewGuy]

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]

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.