BETWEEN DATES SUM

Hi,

Col A has dates in order
Col B has  amounts

Cell D1 is the beginning date
Cell E1 is the end date

I want to sum Col B where the dates in ColA are between the dates in D1 and
E1.

Thanks for your help.

Jerry


0
jkinder (26)
11/24/2004 3:22:36 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
732 Views

Similar Articles

[PageSpeed] 11

I believe this should do it:

=SUMIF(A:A,">="&D1,B:B) - SUMIF(A:A,">"&E1,B:B)

tl


"Jerry Kinder" wrote:

> Hi,
> 
> Col A has dates in order
> Col B has  amounts
> 
> Cell D1 is the beginning date
> Cell E1 is the end date
> 
> I want to sum Col B where the dates in ColA are between the dates in D1 and
> E1.
> 
> Thanks for your help.
> 
> Jerry
> 
> 
> 
0
tjtjjtjt (488)
11/24/2004 4:01:03 AM
You might try this instead. I tested my first formula this morning and got an 
incorect result.
=SUMPRODUCT(--(A1:A100>=C1),--(A1:A100<=D1),B1:B100)

tj

"Jerry Kinder" wrote:

> Hi,
> 
> Col A has dates in order
> Col B has  amounts
> 
> Cell D1 is the beginning date
> Cell E1 is the end date
> 
> I want to sum Col B where the dates in ColA are between the dates in D1 and
> E1.
> 
> Thanks for your help.
> 
> Jerry
> 
> 
> 
0
tjtjjtjt (488)
11/24/2004 12:07:02 PM
Hi,

Thanks,  I will try it this weekend after the holiday is over.

JK

"tjtjjtjt" <tjtjjtjt@discussions.microsoft.com> wrote in message
news:BAE9C416-ED28-4089-B292-3BC34C1A25CE@microsoft.com...
> You might try this instead. I tested my first formula this morning and got
an
> incorect result.
> =SUMPRODUCT(--(A1:A100>=C1),--(A1:A100<=D1),B1:B100)
>
> tj
>
> "Jerry Kinder" wrote:
>
> > Hi,
> >
> > Col A has dates in order
> > Col B has  amounts
> >
> > Cell D1 is the beginning date
> > Cell E1 is the end date
> >
> > I want to sum Col B where the dates in ColA are between the dates in D1
and
> > E1.
> >
> > Thanks for your help.
> >
> > Jerry
> >
> >
> >


0
jkinder (26)
11/26/2004 2:38:02 AM
=SUMIF(A1:A9,"<="&DATE(2004,4,2),B1:B9)-SUMIF(A1:A9,"<"&DATE(2004,3,29),B1:B9)

Try something like the above. 

A1:A9 would reference your dates to be summed. 

B1:B9 references the column to be summed due to the specified date range.  

Of course you would need to set your range in the identical format.  

Hope this helps you out

-- 
Message posted via http://www.officekb.com
0
forum (466)
11/28/2004 4:02:13 AM
Reply:

Similar Artilces:

pivot table add date columns
Hi I have a 24 month spreadsheet, and would like to make 2 new calculated columns, for dates adding 12 months together each, is this possible thanks tim the spreadsheet is date, customer product dollars Hi Tim, This is possible - you have two options: 1) On the Pivot Table toolbar, Formulas>Calculated Field. In the "name" field, enter the name you want to call it, and in the "Formula" field, enter the formula you want it to calculate (Sep04+Oct04+Nov04 etc.) and hit OK. Create all the new fields you want using this screen. When you go back to you Pivot Table...

Sum of counted values in a query
Hello, My question,.... I have a query which count the number of records in a table (as a result of the menuoption view-> totals ->count) as a result of the group by function for unique records. The result is: column1 column2 A 10 B 15 C 8 Now I want to insert a column3 which gives the sum of that counted values. I'd tried something like: expr1:Sum(Count[column2]) The result should be for each record in column3: 33 (result of the sum 10+15+8) It doesn't work. Somebody knows a solution ? Thanks regards, Johan You can always writ...

SUM in random files in the folder
I have files in the folder. how I consolidate in new sheet all the files, to sum for region, products? OBS: I not know the amount of files in this one folder, so I know that the these nominated archives for region, a994.xls region A, b423.xls b323.xls region B .... How to create a look with filename random ? and I not know the quantity file for region. But, all the worksheets have format the same. Necessary they to charts with it, then I want create the table consolidate thanks Marina ...

Sorting by Date (With a Twist)
A coworker gave me a list of data. One of the columns of this data was a date field formated custom in mm/dd/yyyy format. The problem was that when my coworker sorted the date field it was sorting by the day. No problem I thought, so I created a new column typed in =year(e2) which was the cell of the first date. To my suprise the data came back as 1905. The date in E2 was 5/16/2003. If I did =month(e2), I got January (in mmmm) format. What gives? -- B. Baumgartner ------------------------------------------------------------------------ B. Baumgartner's Profile: http://www.excelf...

Charts sums 2 data series
Have 2 col of data and names 2 data series. But the second series is a total of the first no. plus the second no. I want each col to be a separate line on the chart. Right click on the chart, choose Chart Type (or Change Chart Type depending on Excel version), pick the first subtype, which is just arbitrary series, not the second, which is stacked, or the third, which is stacked to 100%. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Sig74462 wrote: > Have 2 col of data and names 2 data series. But the second series is a total > of the fi...

sum count of
Hello experts, I have a query that counts a particular text field using 'Count' and 'Group By'. I then have a report based on this query. Everything works fine; however, I want to sum the counts in my report. E.g., Shape CountOfShapes square 2 circle 5 triangle 3 TotalCountOfShape = 10 I have an unbound textbox and I've tried =sum([CountOfShapes]) but I get a #Error message. Thoughts? alex Alex, What is the name of the calculated control? =sum([CountOfShapes]) Make sure the name is not "CountOfShapes." If the name of...

Changing Start Date for a Loan in Money 2004
I am trying to include last years payments in a home loan but Money 2004 wont let me change the start date earlier than 1-1-2003 - not sure why - I have also deleted the loan and reinput it trying to use a 2002 start dat - again it doesnt allow me to go back that far to start the loan. Do I need Money 2003 to do this? or is there any wayto change the start date without getting the error message? Thanks You'll need to set the system date to sometime last year, and then you'll be able to add the start date. Once you're happy, reset the date. Don't forget to take a backup first,...

Coding Today's Date In Visual Basic
I'm writing some code in Visual Basic and want to to be able to name a sheet today's date whenever the macro is run? Is there a way to do this? lj wrote: > I'm writing some code in Visual Basic and want to to be able to name a > sheet today's date whenever the macro is run? Is there a way to do > this? if you want to do this you are going to have to remove the "/"'s from the date. Excel will not allow tham to be part of a worksheet name. Worksheets.Add.Name = Format(Date,"dd-mmm-yyyy") -- HTH Bob Phillips (replace somewhere in emai...

Lost mail after system date change.
Hello, One of our users is missing all mails from his inbox after an idiot thought it would be amusing to change his system date to 2024. Other than getting hundreds of recurring appointments appearing there appears to be no trace of any mails that were residing in his inbox. I've had a look through his pst file in case this chain of events triggered an auto-archive but can't find any of the elusive mail. I wasn't there at the time this happened so can't provide any info as to any warnings that appeared. Just wondering if anyone can shed any light as to what might have hap...

Problem with date base units for x axis
The problem is that if you have dates as the X-axis series for a chart, the only options for Base Units are Days, Months or Years. If you have quarterly data, excel adds extra space between the data points. A simple example illustrates the problem: Create a column chart from the following data Quarter Value 31-Mar-04 3 30-Jun-04 6 30-Sep-04 9 31-Dec-04 12 Note that the dates to appear on the X axis are the end of each quarter (every 3rd month) Create a simple Column chart from the data. Right-click on the data series and choose Format Data Series, O...

Show date of activity in Contacts?
When I view Activities for a Contact, the only dates shown are the dates they were created, shown under Sent and Received, not when they occured or are scheduled. Is there any way to show the date of the activity and sort by it? I've tried adding "Due By" to the fields but it shows "None" even when there are due dates. Thanks. -- Lew THere is no one date field that works for all types of activity items. = You'd have to use different fields for appointments, tasks, and journal = entries. And you'd have to repeat the process every time you display the =...

Sum of the Multiplications of Two or more Dynamic Ranges
Hi, I was wondering if anyone cld help me out on formulas to be used in Excel. The specifics are I hve defined two Dynamic Ranges say Price and Qty and I want to Sum the product of these two Dynamic Ranges into a cell. I thought using Array formula Ctrl Shift Enter on =Sum(Price*Qty) should give me the required sum but instead it gives me the following error msg #VALUE! How do I achieve my objective which is to get the sum of the product from both these Dynamic Ranges, Price and Qty. Thank u in advance 4 yr help. Cheers With =Sum(Price*Qty) you need to commit it with CTRL+SHIFT+ENTER as i...

Wrong "Today's Date"
I have Money 2003 and it is showing the incorrect date as the "Today's Date". It is exactly 1 month ahead, as today it is saying that it is September 7, 2003. This is causing most of my bills to show as overdue, even though they aren't due until the end of August. I don't know why it changed, but all I care about is fixing it. Can someone help? >-----Original Message----- >I have Money 2003 and it is showing the incorrect date as >the "Today's Date". It is exactly 1 month ahead, as today >it is saying that it is September 7, 2003....

Calculating a difference between an absolute value and a sum of va
My query needs to show time remaining from "eligible time off" and accumulated time taken. However, rather than subtracting the total of time taken from the eligible time off (ex. 120-80), it is running the subtraction on each individual time off request and then adding the results (ex 120 -8, 120-8, 120-8, and coming up with 336). It is evaluating against a query the correctly sums the time off requests. Any help? I am very new to Access and have no familiarity with SQL. newby wrote: >My query needs to show time remaining from "eligible time off" and ...

Great Plains SOP Table Date Fields
I looking for clarification on what DOCDATE and ORDRDATE are in the SOP tables. I believe that DOCDATE is the date the line was entered and that the ORDRDATE is the date that the date that the Order goes into effect. Could anyone clarify this for me? Go to the SDK and look at SOPTRX.RTF. It is the Sales Order Transaction flow by table and field. "Chris Hornung" wrote: > I looking for clarification on what DOCDATE and ORDRDATE are in the SOP > tables. I believe that DOCDATE is the date the line was entered and that the > ORDRDATE is the date that the date that the...

MS Money 2003, Dates on Transaction Forms
I am running MS Money 2003 Standard. Frequently while paying bills in my checking account when I click on Common Withdrawls/Upcoming Bills, and click on a bill, the date window in the transaction form displays a very old date, rather than the current bill "due date". How can I cure this problem? Thank you, Charles ...

Missing Dates
Hi all I have a table called tblCalendar which just has one field called [CalDate], it has a record for every work day (Mon-Frid) between a Start and End Date (run via code). I have a query called [qry Itinerary Dates Check] which lists all Activity records between the same Start and End Date, the columns of interest are: [ReviewDate] and [Specialist]. What I now want to do is check that EACH specialist has a date in the query for ALL dates in tblCalendar. I want to view any Specialist and Dates that are 'missing', if there are any. I found a similar forum thread an...

Best Method Of Storing Up To Date Website Imgaes
Good afternoon, I am very rusty at Access and did do lot of reseach before asking this question. I have a table that stores details of companies called companies. The Primary index of the table is an autonumber called Company_Id and stored within the table is information such as Web-Site First Mini question is..is there a nice way of storing the website information in the right format eg input mask. I want to store the web-page image of each company on a form with different tabs e.g. Company Employees, General Information etc. So far, I found a routine which creates a table with l...

sum on set conditions
I would appreciate help in resolving the following problem Column A ColumnB Column C Column D r1 y04 y 1 r2 y03 y 1 r3 y04 n 0 r4 y03 n 0 r5 y04 y 1 I have been able to us the following formula on column D for counting with "y04" sumif(b1:b5,&...

Fixed Assets Year to Date Depreciation
We would like to enter an asset and not have YTD depreciation charged for the current year in the Asset Book window (for the Current Fiscal Year which is been setup in the Book). We do not want to set the Current Fiscal Year to the next year and then calculate depreciation for the current year, i.e. on the last date of the current year. We would like to depreciate in the current year and not have the YTD depreciation calculated in the Asset Book window. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for ...

subtotals and dates
I have a list of information that I want to insert subtotals for. I want a monthly subtotal for the information but my dates in the spreadsheet exist in the format dd/mmm/yy. Consequently when I use the subtotal function it views each day as a change in date and just counts the number of incidents per day as opposed to per month which is what I want. I want it to look at the change in the mmmm part of the date and subtotal as oppsed to the change in the dd part. Thanks in advance -- mrsatroy One way, assuming field headers are in Row 1 and data starts on Row 2 and dates in column D C...

Convert date/time value to decimal hours
I'm trying to convert a column af date-time values, e.g.,09/02/2005 16:31 to decimal hours. But I can't seem to get rid of the dd/mm/year compoent. What I want to get is 232.517 all by itself. Can someone tell me how to do that? Thanks, E-Jo What's the logic in getting 232.517 from that value? If your date is Sep 9 2005 the only way you would get that is if you subtracted it from another date like Sep 1 2005 00:00 so if you always want to get the days from the first of the month and add the hours you can use =DAY(A1)*24+MOD(A1,1)*24 where the date value is in A1, note ...

Date Cell Corruption
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I have found a &quot;bug&quot; in the new excel which means if I copy and paste a date or date range, from an earlier version of Excel (ie an older spreadsheet), the date in the 2008/new excel changes to a date 4 years and 1 day more than the original date. Even if I try to link the cells between new and old excel, the returning date value is incorrect. Does anyone know how to fix this???? HELP!!! Sorry to burst your bubble :-) but you haven't discovered a bug. What you've discovered is that th...

Annotation and Invoice Dates
Hi, I'm desiring to append note dates and invoice dates from another database into CRM 3.0. According to the Metadata browser this is not supported. Is that true? If so, any other suggestions to preserve the years of account history from our other apps? ...

Date Format Squirrelly
This must be simple, but it's driving me crazy. I have a date in one spreadsheet -- 07/21/05 for example. When I copy the cell and paste it into a second spreadsheet it shows as 07/20/01. Hmmm... Ok, I played around with pasting it various ways (e.g. value only, format only, etc.) and by manually telling the second sheet to format it this way and that, but I still get two different dates. So then I go in and tell both spreadsheets to format the date as a number. They both display the same number 37092. So then I go back and tell both spreadsheets to reformat the cell as mm/dd...