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

  • Thread starter Thread starter John13
  • Start date Start date
J

John13

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
 
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
Click to expand...

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
 
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- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
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.
 
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?
Click to expand...

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
 
=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.
 
Back
Top