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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John13 - 06 Aug 2007 01:33 GMT

    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

Reply to this Message

Ron Rosenfeld - 06 Aug 2007 01:51 GMT

> A B
>1 DATE AMOUNT
[quoted text clipped - 16 lines]
>
>John13

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

Reply to this Message

Dave Peterson - 06 Aug 2007 02:43 GMT

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

> A B
> 1 DATE AMOUNT
[quoted text clipped - 16 lines]
>
> John13

Signature

Dave Peterson

Reply to this Message

John13 - 06 Aug 2007 12:38 GMT

> Another one...
>
[quoted text clipped - 35 lines]
>
> - Show quoted text -

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

Reply to this Message

Dave Peterson - 06 Aug 2007 12:58 GMT

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.

> > Another one...
> >
[quoted text clipped - 47 lines]
>
> John13

Signature

Dave Peterson

Reply to this Message

Ron Rosenfeld - 06 Aug 2007 15:12 GMT

>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?

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

Reply to this Message

Rate this thread: