Excel - SUMIF between two dates (or a specific Month & Year)

Asked By John13 on 05-Aug-07 08:33 PM
A	               B
1	DATE	    AMOUNT
2	4/10/2006	$36.52
3	2/16/2007	$45.12
4	3/14/2007	$65.33
5	4/20/2007	$29.15
6	4/21/2007	$45.20
7	4/22/2007	$47.15
8	5/14/2007	$41.03
9	5/15/2007	$16.21
10	Apr-07	           $0.00

I would like to SUM April of 2007's amounts.  I would like to put the
month I am looking for in a specific cell (say A10 in this example)
and the formula in B10 would SUMIF column A has a date in the range of
April 1 to April 30, 2007 and return $121.50.

Thank you for any help.

John13




Ron Rosenfeld replied on 05-Aug-07 08:51 PM
In A10 above, you have some date in April.  Assuming the date is in the year of
concern, (e.g. 1 Apr 2007 or 7 Apr 2007), then:

=SUMIF(A2:A9,">"&A10-DAY(A10),B2:B9)-SUMIF(
A2:A9,">"&DATE(YEAR(A10),MONTH(A10)+1,0),B2:B9)
--ron
Dave Peterson replied on 05-Aug-07 09:43 PM
Another one...

=SUMPRODUCT(--(TEXT(A2:A9,"yyyymm")=TEXT(A10,"yyyymm")),B2:B9)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


--

Dave Peterson
John13 replied on 06-Aug-07 07:38 AM
Thank you Ron and Dave, both SUMIF and SUMPRODUCT work just as I need.
But Ron, I don't fully understand the ">" portion of the formula.  The
A10 cell is where I want to be able to put a Month and Year so I can
pick up any expense within a specified month. So by you formula are
you treating the date as text and not a date value range? I think your
formula identifies the date and by the YEAR and MONTH portion of it
set the criteria.  Am I close?

Again thank you for helping me with this.

John13
Dave Peterson replied on 06-Aug-07 07:58 AM
I'm not Ron, but his formula has 3 main parts:

=SUMIF(A2:A9,">"&A10-DAY(A10),B2:B9)
-SUMIF(A2:A9,">"&DATE(YEAR(A10),MONTH(A10)+1,0),B2:B9)

First, create a new worksheet (for testing)
Put any old date in A10 and put this in B10:
=a10-day(a10)
and format as a date.

Then put this in C10:
=date(year(a10),month(a10)+1,0)
and format as a date

Then change A10 to different dates.

You'll see what those formulas do.

The third portion of Ron's formula is a way to sum things that are trapped
between two values.

Suppose you have a list of 1000 whole numbers each between 1 and 25.  But you
want to sum the numbers that are between 7 and 10.

You could could sum all the ones >6
then sum all the ones >25

Then subtract these values.


--

Dave Peterson
Ron Rosenfeld replied on 06-Aug-07 10:12 AM
As I wrote, my formula assumes you have a true date in A10.

When you enter, for example, 4/2007, Excel will parse that into 1 Apr 2007.  By
the way, if you just enter 4/07, and you are using US Regional Settings
(control panel stuff), Excel will parse that as 7 Apr 2007, so you might want
to be careful how you enter a date.

In any event, with any date of a month in A10, the formula =A10-DAY(A10) will
always give a date that is the last day of the preceding month.

So the criteria argument ">"&A10-DAY(A10) will evaluate, in words, to "any date
that is greater than the last day of the preceding month".

If the date were, indeed, text, the formula would not work as written.
--ron
Alan Price replied to John13 on 28-Dec-10 04:58 AM
=IF(I31=EOMONTH(I31,0),SUMIFS($P$22:$P$382,$I$22:$I$382,">"&EOMONTH(I31,-1),$I$22:$I$382,"<="&EOMONTH(I31,0)),0)



Where:

I31 = date in row (System Format 'Date')

P range = (the column with values you want to add up)

I range = (Date range within which you would like to add or subtract)



The formula looks for values between the month end of the previous month and the current month end.



You can change this to suite your date needs, many examples already exists.
Alan Price replied to John13 on 28-Dec-10 05:03 AM
=IF(I31=EOMONTH(I31,0),SUMIFS($P$22:$P$382,$I$22:$I$382,">"&EOMONTH(I31,-1),$I$22:$I$382,"<="&EOMONTH(I31,0)),0)



Where:



I = cell with a specific Date

P range = values to be used during calculations

I range = Series of dates in a coloumn



The formula looks at adding values between the previous month end and the current month end.