SUMIF and a range of dates- ??

[Andrew Cushen]

Hi all -

I have a set of dates in column A, and corresponding
values in column E. What I want to do is write a formula
that will sum all column E values whose corresponding
column A date falls in June. The dates are formatted like
this: 6/3/2004 . I have no problem when I specify one date
in the SUMIF formula, like so:

=SUMIF(A11:A50,"6/3/2004",E11:E50)

or even this short version works:
=SUMIF(A11:A50,"6/3",E11:E50)


I have tried various combinations like:
=SUMIF(A11:A50,"6/1" OR "6/2" OR "6/3",E11:E50)

=SUMIF(A11:A50,OR("6/1","6/2","6/3"),E11:E50)

=SUMIF(A11:A50, (>= "6/1/2004" AND <= "6/30/2004"),E11:E50)

and I've tried formatting the date like #6/1/2004#
or "#6/1/2004#". Nothing works. As long as I specify an
exact date, like "6/3/2004", or even a short version
like "6/3", it works fine, but only gives me values for
the one date (when I specify an exact date) or dates
starting with the number I specify (if I specify "6/3", it
gives me values for dates "6/3/2004" and "6/30/2004").

Is there any way to achieve what I'm after? It seems like
there must be, but a long search of the Excel Help turned
up nothing of any use...

TIA,

-Andrew
=========================================================

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$11:$A$50>=F11),--($A$11:$A$50<=G11),$E$11:$E$50)

where F11 houses 1-Jun-2004 as a true date and G11 the formula:

=DATE(YEAR(F11),MONTH(F11)+1,0)

 

[Domenic]

Hi Andrew,

Try,

=SUMPRODUCT(--(MONTH(A11:A50)=6),--(YEAR(A11:A50)=2004),E11:E50)

Hope this helps!

 

[Bob Umlas]

Try
=SUMPRODUCT((A11:A50>="6/1")*(A11:A50<="6/3")*E11:E50)

Bob Umlas
Excel MVP

 

[William]

Try

SUMIF(A:A,"<"&DATE(2004,7,1),E:E)-SUMIF(A:A,"<"&DATE(2004,6,1),E:E)

--
XL2002
Regards

William

(e-mail address removed)

| Hi all -
|
| I have a set of dates in column A, and corresponding
| values in column E. What I want to do is write a formula
| that will sum all column E values whose corresponding
| column A date falls in June. The dates are formatted like
| this: 6/3/2004 . I have no problem when I specify one date
| in the SUMIF formula, like so:
|
| =SUMIF(A11:A50,"6/3/2004",E11:E50)
|
| or even this short version works:
| =SUMIF(A11:A50,"6/3",E11:E50)
|
|
| I have tried various combinations like:
| =SUMIF(A11:A50,"6/1" OR "6/2" OR "6/3",E11:E50)
|
| =SUMIF(A11:A50,OR("6/1","6/2","6/3"),E11:E50)
|
| =SUMIF(A11:A50, (>= "6/1/2004" AND <= "6/30/2004"),E11:E50)
|
| and I've tried formatting the date like #6/1/2004#
| or "#6/1/2004#". Nothing works. As long as I specify an
| exact date, like "6/3/2004", or even a short version
| like "6/3", it works fine, but only gives me values for
| the one date (when I specify an exact date) or dates
| starting with the number I specify (if I specify "6/3", it
| gives me values for dates "6/3/2004" and "6/30/2004").
|
| Is there any way to achieve what I'm after? It seems like
| there must be, but a long search of the Excel Help turned
| up nothing of any use...
|
| TIA,
|
| -Andrew
| =========================================================

 

[Ron Rosenfeld]

I have a set of dates in column A, and corresponding
values in column E. What I want to do is write a formula
that will sum all column E values whose corresponding
column A date falls in June. The dates are formatted like
this: 6/3/2004 . I have no problem when I specify one date
in the SUMIF formula, like so:

If you are looking for the values between a range of dates, say the range of 1
Jun 2004 - 30 Jun 2004 inclusive, then the form is:

=SUMIF(dtrange,">="& Date(2004,6,1), rngToSum) -
SUMIF(dtrange,">"& Date(2004,6,30), rngToSum)

Any unambigous date expression will work.


--ron

 

[Andrew Cushen]

Wow!
That was fast!

Thanks to everyone who replied; I got William's method
working, as it was closest to my original idea. I
appreciate all the replies; it seems there are always many
ways to approach a problem in Excel, and I found it
enlightening to see all the different ways that the job
could be done.

Thanks again, all!

-Andrew
==================================================