How to total items if they fall between a date range

I have a range of items that fall under a date range.  
I am trying to total the items that fall under a certian date range.

ie.
        20-Apr-05        25-Apr-05     1-May-05     16-May-05
A       $1.00              $0.00            $0.00           $0.00
B       $19.00             $0.00            $10.00         $0.00
C       $0.00              $5.00            $0.00           $0.00
D       $0.00              $0.00            $0.00           $20.00
total   $20.00            $5.00            $10.00         $20.00

If dates fall between 1-Apr-05 and 30-Apr-05, total totals = $25.00
If dates fall between 1-May-01 and 31-May-05, total totals = $30.00
If dates fall in the year 2005, total totals = $55.00

How do I do this?

0
Cel (4)
5/17/2005 5:15:48 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
622 Views

Similar Articles

[PageSpeed] 40

On Tue, 17 May 2005 10:15:48 -0700, cel <cel@discussions.microsoft.com> wrote:

>I have a range of items that fall under a date range.  
>I am trying to total the items that fall under a certian date range.
>
>ie.
>        20-Apr-05        25-Apr-05     1-May-05     16-May-05
>A       $1.00              $0.00            $0.00           $0.00
>B       $19.00             $0.00            $10.00         $0.00
>C       $0.00              $5.00            $0.00           $0.00
>D       $0.00              $0.00            $0.00           $20.00
>total   $20.00            $5.00            $10.00         $20.00
>
>If dates fall between 1-Apr-05 and 30-Apr-05, total totals = $25.00
>If dates fall between 1-May-01 and 31-May-05, total totals = $30.00
>If dates fall in the year 2005, total totals = $55.00
>
>How do I do this?

Assume your row of dates is NAME'd "Date"  and your total row is NAME'd
"total".

The formula would be:

=SUMIF(Dates,">="&StartDate,total)-SUMIF(Dates,">"&EndDate,total)


--ron
0
ronrosenfeld (3122)
5/17/2005 6:30:51 PM
Reply:

Similar Artilces:

Capitalizing a Date
How do we format a cell so that the date will all be in uppercase (caps) versus proper caps (only first letter of month or weekday capitalized)? hi, ! > How do we format a cell so that the date will all be in uppercase (caps) > versus proper caps (only first letter of month or weekday capitalized)? if you don't want to use a helper cell/column with upper(... worksheet function... you might want to give a try to the following: if available, change the font for your range/worksheet/workbook/style/... this fonts shows text un uppercase: - Castellar - Engravers MT - Felix Titling...

Due dates not showing correctly in Outlook Today
I have recently changed the due dates for some tasks in Outlook 2002 but in Outlook Today view, all the dates are showing as one day previous. Why is this? New tasks do the same thing, eg a new task with a due date of 20 May appears on Outlook Today as having a due date of the 19th. The due date appears correctcly in Task and Calendar view, it's just Outlook Today where it's wrong. I've checked obvious things including the system date and time zones times to no avail. I've been using Outlook 2000 quite happily for ages and have recently upgraded to Outlook 2002 th...

How to Match and Sort two range of data?
Hi all, Appreciate if anyone could help out on this issue. I have a range of datas on sheet 1 and another range of data on sheet 2. Sample as below : In sheet 1, Company Name Sales Fig. D Limited 4000 A Limited 1000 E Limited 5000 B Limited 2000 C Limited 3000 In sheet 2, Company Name Sales Fig. E Limited 5000 D Limited 4000 C Limited 300...

chart with fixed range on side and bottom
Is it possable to make a chart with a fixed range on the side and bottom as if it was printed? if so how do we do it, I'm using office 2007 but also have 2003 on a second machine. Please clarity "fixed range" and "as if it was printed" You are aware that you can format chart axis and set the max and min values of each one? Or do you mean you want the chart to have a certain size? If so, see http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html Need help with VBA, see David McRitchie's site on "getting started" with VBA http://www.mvps.org/d...

The new version of MSPOS needs to have Parent Child Items
I have customers that would really like to see the Parent Child Relationship with the new version of MSPOS. Pretty basic functionality that we need to have the product compete in the market place. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.c...

Sent Items not appearing after mailbox transfered from one MS Exchange 03 server to another
I just transfered a users mailbox from one MS Exchange 2003 server to another within the same domain. The transfer was successful on the first time, however when the user goes in and attempts to bring up her "Sent Items" in outlook it just hangs there and says Loading.... Does anyone have any ideas on why this is happening and what I can do to resolve it -- again, the mailbox transfered just fine using the MS Exchange Task / Move Mailbox Wizard. Thanks everyone in advance for your assistance. Are you using classic or cached mode? Did you repoint the profile to the new server? ...

Date Update
Hi All How do I STOP Word updating a date field when i load the documents some time after the original create. I Want the original date BTW - Update Automagically on the Date window is not ticked Thanks Pete Use Alt+F9 to toggle the display of the field codes and change the type of field to { CREATEDATE }. It will then display the date upon which the document was created. If you put such a field in the template from which you are creating the documents, and you use File>New and select the template as the basis of the document that you want to create, the field wi...

Pivot table
Hi I have created a pivot table from an external database to show Gross Profit and the percentage per Sales Rep and Customer The items Salesperson then Customer are in the rows and in the column I have months. The data is sales, costs, gross profit, and gross profit percentage. What is happening in the grand total column is that the subtotal GP% is sometimes showing 0.00% instead of the actual percentage. Could someone please let me know who I can solve this. Also when there is no sales value against an item I don't want the GP% to be calculated. I would prefer the field to be bla...

Count occurences over range if a certain criteria met
Hi I have a named range, called Servers (B2:I50). Within this range I want to count how many entries begin with New, so figure I need to use Left somehow, but am not sure how I can use this in a single cell with some countif/sumproduct function to count them all. Can anyone help please? B C New1 Server C Server A new3 Server B Server E Server C Server F New2 Server G Server B Server D Server A New 4 So this would return 4. Cheers Rich =COUNTI...

Macro that modifies a data range for a chart
How do you build a macro that will modify your data range to pick up most current week data? I have over 50 graphs that are all set up to be updated each week. The data range that it’s referencing is already populated as it’s linked to another workbook. Below is what the data range looks like. As the week passes the #DIV/0! gets updated to a number. WEEK Data Data 2 P5W3 740 794 P5W4 765 794 P6W1 828 848 P6W2 #DIV/0! 848 P6W3 #DIV/0! 848 P6W4 #DIV/0! 848 ...

subtracting dates
I am using Excel 2000 In the language of Excel, dates are actually numeric values based on the number of days since January 1, 1900. i.e., the date 12/30/2003 is actually the value 37985, or 37,985 days since January 1, 1900....or at least that's how I understand it. Because of this nifty bit of info, you can add and subtract dates. *My question, is there a formula that says ONLY perform the subraction IF dates are present in both cells.* I explain below. Try the following, when you have time. Open a new worksheet. - in A1 type the following formula - =A3-A2 - In A2 type 12/30/...

Office 2004 shipping date?
Anyone know when the new office 2004 for Mac is shipping? >Anyone know when the new office 2004 for Mac is shipping? This is quoted from the Amazon.com site: " Availability: This item will be released on May 10, " 2004. You may order it now and we will ship it to you " when it arrives. -- Bernard Rey MVP office:macintosh ...

New User
I'm building a tracking database. How do I create a date and time stamp for each time some updates my form. I want to includ the name, time and date they last updated the form. Is this possible? I've only been using this for about 2 weeks. Can someone help me please. Get the Windows username as a function an set the value of the lastuser field to it.: http://www.mvps.org/access/api/api0008.htm write the Now() function to the lastupdated timestamp field. You may use the BeforeUpdate event to write the values, something like (air code): Sub Form_BeforeUpdate(Cancel As Integ...

Changing Publication Date and Pg. Nos.
I've created a newsletter using a Publisher template. At the top of each page, the template automatically inserts the volume and issue number on one side and the page number on the other side. I want to have the volume and issue number at the left and the date at the right on the first page. On the inside pages, I want the page number, newsletter name, and date to be at the top of each page, with the publication name centered and the page number and date alternating positions (because the newsletter will be printed front and back). I've had no success trying to find the wa...

Pivot table has an item called Formula1
A pivot table has an item called Formula1, but I do not see such an item in my list on which the table is based. What is the reason and how do I find out what is causing it Thanks Laurence The PivotTable contains a Calculated Item for one of the fields. To see the formula, select a cell in the pivot table. On the pivot toolbar, choose PivotTable>Formulas>List Formulas This will insert a new sheet in the workbook, with a list of formulas in the PivotTable. The items that are used in the formula should help you determine which field has the calculated item. Right-click on the...

Name Ranges/ How do I print these?????????????????
I am creating "Name" ranges by utilizing the Insert/Name drop down menu option. I see the name I created and the corresponding range area. How do I print this specific Names ranges? Any help would be greatly appreciated. Carlos Carlos, Go to a blank area of your worksheet and try Insert|Name|Paste and select Paste List. That should generate a list of all named ranges for the sheet. You can then print that range. PC "Carlos Feliciano" <carlos_feliciano@concentra.com> wrote in message news:02f601c357aa$c81f7ef0$a601280a@phx.gbl... > I am creating "...

Number Range Format Type
I want to have a format for my cells that basically means "the data in this cell specifies a range of integers". For example, it might say 1-4, or 2-7, etc. Or it might just say 1 which is the same as 1-1 (low val is 1 and high val is 1). Is there a way I can have a format code for this and use a custom format? Later on in the worksheet, I want to be able to extract the min and max values of this range from the cells. The application of this is that a certain column is going to be used for holding ranges of numbers. Then at the very bottom I want to add up the ranges to produce...

Detecting missing dates....
> 5/7/04 16:03 Roger > 5/10/04 9:02 Roger > 5/10/04 15:59 Roger > 5/11/04 8:57 Roger > 5/11/04 15:59 Roger > 5/12/04 9:06 Roger > 5/12/04 15:59 Roger > 5/14/04 8:19 Roger > 5/14/04 16:30 Roger > 5/16/04 9:27 Roger > 5/16/04 16:12 Roger Above is part of a list of time and dates at which Roger went to a office. Is there a way to detect / find out on which day, Roger didn' go to the office? As u can see, 5/13/04 and 5/15/04 are missing, becaus he didn't use his attendance card.....So how do I find out on whic dates he was absent using Excel -- Message ...

FILTERING A FORM WITH A DATE
Hello! I always have a problem when opening a form with a date filter or condition parameter. The format that I always use is "dd/mm/yyyy" but when the form opens it changes to "mm/dd/yyyy". My XP windows short date setting is already in "dd/mm/yyyy" format. Could anyone please help me? Thank you. Ramon Hi Go to Control Box and check the Regional Settings for both date and times, ensure they are set to UK (I assume thats what you need). -- Wayne Manchester, England. "ramon" wrote: > Hello! I always have a problem when opening a form ...

how do i enter a date quickly without using slashes or dashes?
i want to enter a date by only using number keys but i want the outcome to have the slashes in it i.e. 060204 --> 06/02/04. just like if you enter a currency like 602.04 it comes out like $602.04 and you don't have to add the $ because of how the cell is formatted. Date And Time Entry http://www.cpearson.com/excel/DateTimeEntry.htm -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "vodoris" <vodoris@discu...

Counting Instances Of Item In A Column
I have a table with data along the lines of the below. A_______B______________C_______D 1_______Success/Fail_____Type_____Time 2_______S______________X_______34 3_______F______________Z _______45 4_______S______________Y _______43 5_______F______________X_______34 6_______S______________Z_______34 I want to work out the success rate for each type, which I thought would be easy but has proven surprisingly difficult for an Excel novice. Obviously to work out the success rate for type Z I would need to count the number of Zs in column A and the number of successes S corresponding to each Z an...

date operations in Excel 2003
Are you aware of a problem making operations on excel 2003 dates ? even if the cell format is Date , a comparison of dates (like is 21/08/2004 > 22/08/2004) gives incorrect results and no error message. If you edit the cell containing the date by pressing F2 and immediately Enter, than the formula gives the correct result. Any idea ? Hi this is probably due to the fact that your current values are not really stored as dates. Do you import these date values? "bernard" wrote: > Are you aware of a problem making operations on excel 2003 dates ? even if > the cell f...

Attn: Allen Browne - Old Question
Allen - I haven't tried doing the response that you gave me regarding the question below. I'm having a major senior moment and can't figure out what the DLookup syntax would be. Any help? BTW the table name is tblContractorProjects... Thanks Jeff ----------------------------------------- Use the BeforeUpdate event procedure of the form to perform the validation. Use DLookup() to see if an overlapping entry exists in the table. Assuming contractStartDate and contractEndDate are required fields (so you don't have to handle overlapping dates when one of the fields is b...

clarification: cumulative to-date total formula needed
I am trying to create a forumula that will give a cumulative or "to-date" total that will exclude future months from the total. i.e. ithe point in time is august and I need to show a to-date number through august only even though september and october are included in the spreadsheet. Next month will need to show through september and exclude october. columns: july august september october year-to-date rows : 100 200 300 400 year-to-date as of august should show 300 if the point in time is august and show 600 if it's september is there a way to write a formula using time o...

Copy one sheet for each pivotfields if items have data
Hello, I have a pivot table showing this data: rowfield1 : ValueDate rowfield2: FundName columnfield1 : Currency columnfield2 : BuyOrSell (has 2 items, Buy and Sell) data : Amount I'm trying (with VBA) : -->to copy one sheet per ValueDate, per FundName, per Currency if the item has an amount data -->to rename the sheet after the fields items (something like "NameA USD 28_07" for the first sheet, "NameA EUR 28_07" for the 2nd, "NameA EUR, 29_07" for the 3rd and so on). I gave it many tries but all I get is the two first sheets created with the right n...