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

	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

0
8/6/2007 12:33:57 AM
excel 39879 articles. 2 followers. Follow

5 Replies
675 Views

Similar Articles

[PageSpeed] 24

On Mon, 06 Aug 2007 00:33:57 -0000, John13 <johnasmith13@gmail.com> wrote:

>	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

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
0
ronrosenfeld (3122)
8/6/2007 12:51:46 AM
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

John13 wrote:
> 
>         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

-- 

Dave Peterson
0
petersod (12004)
8/6/2007 1:43:12 AM
On Aug 5, 9:43 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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
>
>
>
>
>
> John13 wrote:
>
> >         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
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - 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

0
8/6/2007 11:38:15 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.

John13 wrote:
> 
> On Aug 5, 9:43 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > 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
> >
> >
> >
> >
> >
> > John13 wrote:
> >
> > >         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
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - 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

-- 

Dave Peterson
0
petersod (12004)
8/6/2007 11:58:11 AM
On Mon, 06 Aug 2007 11:38:15 -0000, John13 <johnasmith13@gmail.com> wrote:

>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
0
ronrosenfeld (3122)
8/6/2007 2:12:05 PM
Reply:

Similar Artilces:

Saving Monthly Accounts and starting a new Month?
Hi Everyone, I really enjoy the Access Database System it has helped me a lot with my accounts but I must be doing something wrong because, I keep opening new databases for each month instead of it following on from the previous month and using the same "Categories!." So my question is How do I save the previous month and let it be stored, and start a new month on the same system or page but without adding up what has been put in the system... If you know what I`m on about. Thank you Angeldelight... melting in the UK weather :) Hard to advise without some info ...

filter birthday's by month in Excel
I have tried the above by auto filtering, then custom filter, however when i type for example ??/07/?? or **/07/** nothing works. Could you please tell me how to filter birthday's by month as soon as possible. Kind regards, Lisa Peterson I don't know a way to do it directly. As a work-around, you could add a column and fill it down with the formula (assuming the dates are in ColA, starting at A2) =month(A2). Then filter the database based on the new column. Rgds, ScottO "Lisa Peterson" <Lisa Peterson@discussions.microsoft.com> wrote in message news:4C9E7EAE-9FD...

count between a date range
Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2>=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks Try.. =IF(M2>=datecell-DATE(1,0,0),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: > Hi, I want to be able to count be...

Help with Dates
Hi all, I have a table with field name "ConnectionDate". I want to create a form with ComboBox that will show the "ConnectionDate" as month and year only. The value the user will choose will be a criteria for a query. Please help. TIA, Tom ?Format(Date,"mmyyyy") would return 112007 so, SELECT Format(ConnectionDate,"mmyyyy") AS ConnectDate FROM SomeTable -- Dave Hargis, Microsoft Access MVP "Tom" wrote: > Hi all, > > I have a table with field name "ConnectionDate". > I want to create a form with ComboBox tha...

Showing Specific Data from Master Sheet to Another Sheet
I am making a bill of materials at work and have a master list I want to access from other sheets. The master list has all parts named and categorized, and I want to be able to access sections (Hydraulics, Chassis, Electrical, etc.) of the master from other sheets. I have tried pivottables but I am having issues showing the data in it original form: Part Name Quantity Material Expense Category etc. (accross the row) I wish to be able to make calculations only off the selected data on each sheet but I am having immense problems getting there. If you can help. Parker Jo...

Converting Dates from YYMMDD to MMDDYYYY
Hi All; I'm looking for a format function that will convert a date in the text format of YYMMDD to text format of MMDDYYYY. Any help would be GREATLY appreciated! Thanks in advance! You can't do it using a Format function, but try: Function SwitchDateFormat(YYMMDD As String) As String Dim strDay As String Dim strMonth As String Dim strYear As String If Len(YYMMDD) = 6 Then strYear = Left$(YYMMDD, 2) strMonth = Mid$(YYMMDD, 3, 2) strDay = Right$(YYMMDD, 2) If strYear < "30" Then strYear = "19" & strYear Else strYear =...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

Query emails coming to a specific domain
Hello; We have about 5 domains on our exchange server that we receive email from. We are in the process of not renewing one of our domains but would like to check to see if email from that domain is still coming through. Is there any way to do that? I tried the Message Tracking Center with the *.domain.com but it didn't like that. It wanted a specific user. Thanks Check SMTP logs as well. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <nospam@newsgroup.com> wrote in message news:...

Calculate number of months
Hello, I have a field where I am trying to implement a calculation. I want it to take the date in a cell and subtract it from today's date to show me the total number of months between those two dates. So right now, it looks like =(TODAY())-D3 and it returns 167 - the total number of days. How do I make it show me months? THANK YOU IN ADVANCE =Month(Today())-Month(D3) "TxWebDesigner" <beverly@beverlylanedesigns.com> schreef in bericht news:e3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl... > Hello, > > I have a field where I am trying to implement a calculation...

Mid-month credit card downloads
BofA just changed their on-line interface and they're having a lot of problems. I was told that I can no longer download credit card transactions into my Money program mid-month. In other words if I want to track expenses during the month I will have to manually enter all of my credit card transactions. The alternative is to wait until the end of my billing cycle and download 50 to 100 transactions at once and then review and assign them. There are no plans to reinstate this service. I immediately sent a comment to them complaining about the inconvenience and limitations of their new...

DNS Question
Hello All, I am working with a customer that has 2 Small Biz servers in the same office. These are on 2 domains, but the same subnet. I am having trouble getting mail to route between the 2 domains. Each is Small Biz server 2003. Domain A has a public IP of 72.151.19.122 and an internal IP of 192.168.200.78. Domain B has a public IP of 72.151.19.123 and an internal IP of 192.168.200.250. We are using a Netgear firewall and inbound and outbound mail between each of these domains and the rest of the world works fine. They just can't seem to talk to each other. I would think this woul...

enter-key works with two inputboxes but not with olny one.
Hi, There is a form with two inputboxes and a submit button.The focus is on the first inputbox. When the user presses the enter-key without having entered some text, the javascript message "wrong text" appears. This works. But if i remove the second inputbox, leaving only the first inputbox and the submit button, pressing the enter-key without any text does not show the javascript message. Nothing happens. The focus remains in the inputbox. You can see the page is postbacked, but that's all. Why does it work with two inputboxes and not with one? And how to fix...

Automatic Data Entry Script OnChange Event for Date Field.
Hi, Sub: Automatic Data Entry Script OnChange Event for Date Field. has anyone assigned 'Today date'( new Date()) to a custom date filed on the form - OnChange of a Picklist field, I have the following code. DatefiledName.value = new Date(); This assigns, currect date to the field, but then when I save it does not like it. When I manually assign a date it displays in the format mm/dd/yyyy, and saves correctly, so I changed the script to: d= new Date(); s = d.(getMonth() +1); s += '/' + d.getDate(); s += '/' + d.getFullYear(); DatefiledName.value = s; This also acc...

Returning Data from a third cell in same row that meets two other
I'm looking for a formula that will return a result from a cell on a same row as two other cells that meet certain criteria. I'm sure there is a way to do this but i am a novice at this kind of stuff and can't seem to figure it out. For example i want a cell to = what is in column E when column A="36751" and when column B="Total Returns" The spreadsheet has 55000 rows. there will only be one instance where both these criteria are met. i want to use this to create a seperate spreadsheet with just info i need and can update on a daily basis. thanks in ...

Previous year data into chart
I currently have a whole table of data imported into Excel from Access. The only problem is, i cannot seem to import the data for the previous year using a query. With the data i would like to create a chart for the past 12 month, i.e the last 12 cells (as the data is taken monthly). Can i select data for the last 12 months that will remain the last 12 months even when the worksheet is refreshed every month? How my data looks: Date % Acknowledged % Investigated 09/2005 85 99 10/2005 ...

Sumif across a table
I am looking for a function that works using a =sumif function to add things that are not in a range that are next to each other as seen below the letters in () are the columns that the values are in... so I am looking for a sum in column A "X" of the total work out time if the appl column is "Y" total work Running (D) Walking (F) Elliptical (H) out time (C) Appl (D) Time (E) Appl (F) Time (G) Appl (H) Time (I) X Y 20 N 0 Y ...

Merge two excel files
I have two excel files with the same cell layout. Some of the entries in the first file are identical to the second file. How do I merge the two files so that the resulting file has no duplicates? Thanks in advance. Tom -- So long and thanks for all the fish! Tom, Copy the entries from the second file into the first, below the other entries (don't copy the header row). Then save the file under a new name. I'm going to assume that your data starts in cell A2, with headers in row 1, and extends down the page. In a column to the right of the data table, enter a formul...

Counting cells with particular month and year
Thanks to help previously received, I have been using this formula to count the number of referrals received in a particular month. Is it possible to include the year as well? =SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(ISNUMBER(B2:B10))) Now the Year also included in the below formula the year reference cell is referred in B1 cell of STATS sheet. =SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1)*--(YEAR(A2:A10)=Stats!B1),--(ISNUMBER(B2:B10))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Saylindara" wrote: ...

number changes to date ... how do I change
I am trying to do an "age" range: 1-9 10-19 20-29 Instead I get September 9, 2004 or October 19 I can't figure out how to change it. Please help! Thank you! kk Format->Cells->Number Change the type to Text, and it should keep everything the way you want it. If you don't specify a type, Excel takes a guess based on what you enter. Hope that helps. -Bob --- Message posted from http://www.ExcelForum.com/ ...

Two users on a network editing documents simultaneously in word 20
My office just upgraded from Word 2003 to Word 2007. We are on a network and share access to Word files. In Word 2003, if one user tried to open a file in which another user was working, a message would appear stating that the file was "locked for editing by (user)." It seems that with Word 2007, any user can open a file (and edit it) while another user is doing so as well. We do not want this to be the case, and just want the settings to be as they were with Word 2003 (i.e. if a document is in use, another user cannot access it simultaneously). Ideally, we'd lik...

Automatic date/time aquisition for graphs
Ok, I collect particle count data for a cleanroom which looks like th following: "LASAIR110","04/05/19","15:47:41",2700.0,45.00,"V6.3",0,0,0,1,433,486,236,113,294,196,103,328,4.993,1.0000,0.000,0.000,0.000,0.000,0.000,"22de" "LASAIR110","04/05/19","16:33:15",2700.0,45.00,"V6.3",0,0,0,1,458,432,163,55,107,55,46,139,5.007,1.0000,0.000,0.000,0.000,0.000,0.000,2299 "LASAIR110","04/05/19","17:18:49",2700.0,45.00,"V6.3",0,0,0,1,305,286,76,29,14,1,0,0,5.017,1.0000,0.000...

Date Range
When I use the following macro, what reference range is being employed that makes certain decisions for the conversion? For example, when I have a date of 1/12/46, this converts to January 12, 1946, not January 12, 2046; when I have a date of 1/14/20, this converts to January 14, 2020, not January 14, 1920; when I have a date of 2/3//10 this converts to February 3, 2010 not February 3, 1910??? Seems like this was only project so far into the future. I haven't tried all numbers but once you reach that boundary it assigns the 20th century to any number there and above. Su...

Calculating Date Fields
I have a document that is filled with FormFields and users go from field to field filling the fields for a final document. I have been reading up on 'calculated dates' but have not seen a scenario like this. Three of my formfields are "Date1", "Date2" and "Date180." What I am trying to figure out is how to evaluate/compare Date1 and Date2 to see which is the earliest and then provide in Date180 the date 180 days from the earliest date. (Unless the form is blank there will always be a Date1, but there may not be a Date2 and if Date2 exist...

Permitting relaying to specific domain
Platform: Windows 2003, Exchange 2000 sp2. I'm working with two e-mail domains in my exchange server, with the primary e-mail address @yyyy.com, I can send/recieve e-mail using pop3, but I'm not able to do the same with the second e-mail address @xxxx.com, with this address I just can recieve e-mail, but when I try to send I recieve the relay error 550. Any help will be appreciated. Thank You. Create connector for that domain and in Address Space tab set "Allow messages to be relayed to this domains". javier wrote: > Platform: > > Windows 2003, Exchang...

Sumif with two criteria #2
I need to add column c if column a and column b both meet my criteria. example A B C 1 X Y 3 2 S Y 1 3 X Y 2 4 X F 3 5 F Y 3 X AND Y 5 On Thu, 30 Oct 2003 13:06:29 -0800, "danperez@joannstores.com" <anonymous@discussions.microsoft.com> wrote: >I need to add column c if column a and column b both meet >my criteria. > >example > A B C >1 X Y 3 >2 S Y 1 >3 X Y 2 >4 X F 3 >5 F ...