Sumproducts in a date range

Good day,

I was wondering if any one could help me with a problem.

My column A contains dates in a dd-mmm-yy format.  Column C contains a
number of events that took place. I am trying to count the number of
events in a fiscal year.

A...................C
01-Jan-05..........1
02-Feb-05.........1
03-Mar-06.........1
11-Apr-06.........1
09-May-06.........1
11-Jun-06..........1

=SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006))
Will count the events in 2006.

=SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006),--(MONTH(activity!A2:A1000)=7))
Will count the events in July 2006

But I cannot figure out how to get it to count dates in a range - a
fiscal year that runs from March 31, 2005 to April 1st, 2006.

Many thanks to any who could venture a guess!

0
7/11/2006 4:37:37 PM
excel 39879 articles. 2 followers. Follow

3 Replies
342 Views

Similar Articles

[PageSpeed] 41

Hi Ovyd,

=SUMPRODUCT(activity!c2:c1000,--(activity!A2:A1000>=--"31/3/2005"),--(activity!A2:A1000<--"01/4/2006"))

HTH
--
AP

"Ovyd" <graeme_bowles@yahoo.ca> a �crit dans le message de news: 
1152635857.062106.83410@h48g2000cwc.googlegroups.com...
> Good day,
>
> I was wondering if any one could help me with a problem.
>
> My column A contains dates in a dd-mmm-yy format.  Column C contains a
> number of events that took place. I am trying to count the number of
> events in a fiscal year.
>
> A...................C
> 01-Jan-05..........1
> 02-Feb-05.........1
> 03-Mar-06.........1
> 11-Apr-06.........1
> 09-May-06.........1
> 11-Jun-06..........1
>
> =SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006))
> Will count the events in 2006.
>
> =SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006),--(MONTH(activity!A2:A1000)=7))
> Will count the events in July 2006
>
> But I cannot figure out how to get it to count dates in a range - a
> fiscal year that runs from March 31, 2005 to April 1st, 2006.
>
> Many thanks to any who could venture a guess!
> 


0
ardus.petus (319)
7/11/2006 4:53:01 PM
I have been banging my head about that one for so long now.  You are a
lifesaver.  Many thanks!

Cheers!
Ovyd



Ardus Petus wrote:
> Hi Ovyd,
>
> =3DSUMPRODUCT(activity!c2:c1000,--(activity!A2:A1000>=3D--"31/3/2005"),--=
(activity!A2:A1000<--"01/4/2006"))
>
> HTH
> --
> AP
>
> "Ovyd" <graeme_bowles@yahoo.ca> a =E9crit dans le message de news:
> 1152635857.062106.83410@h48g2000cwc.googlegroups.com...
> > Good day,
> >
> > I was wondering if any one could help me with a problem.
> >
> > My column A contains dates in a dd-mmm-yy format.  Column C contains a
> > number of events that took place. I am trying to count the number of
> > events in a fiscal year.
> >
> > A...................C
> > 01-Jan-05..........1
> > 02-Feb-05.........1
> > 03-Mar-06.........1
> > 11-Apr-06.........1
> > 09-May-06.........1
> > 11-Jun-06..........1
> >
> > =3DSUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=3D2006))
> > Will count the events in 2006.
> >
> > =3DSUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=3D2006),=
--(MONTH(activity!A2:A1000)=3D7))
> > Will count the events in July 2006
> >
> > But I cannot figure out how to get it to count dates in a range - a
> > fiscal year that runs from March 31, 2005 to April 1st, 2006.
> >
> > Many thanks to any who could venture a guess!
> >

0
7/11/2006 5:01:50 PM
Glad I could help!
(I discovered SUMPRODUCT via forums a couple of months ago)

Cheers
--
AP

"Ovyd" <graeme_bowles@yahoo.ca> a �crit dans le message de news: 
1152637310.414741.265080@35g2000cwc.googlegroups.com...
I have been banging my head about that one for so long now.  You are a
lifesaver.  Many thanks!

Cheers!
Ovyd



Ardus Petus wrote:
> Hi Ovyd,
>
> =SUMPRODUCT(activity!c2:c1000,--(activity!A2:A1000>=--"31/3/2005"),--(activity!A2:A1000<--"01/4/2006"))
>
> HTH
> --
> AP
>
> "Ovyd" <graeme_bowles@yahoo.ca> a �crit dans le message de news:
> 1152635857.062106.83410@h48g2000cwc.googlegroups.com...
> > Good day,
> >
> > I was wondering if any one could help me with a problem.
> >
> > My column A contains dates in a dd-mmm-yy format.  Column C contains a
> > number of events that took place. I am trying to count the number of
> > events in a fiscal year.
> >
> > A...................C
> > 01-Jan-05..........1
> > 02-Feb-05.........1
> > 03-Mar-06.........1
> > 11-Apr-06.........1
> > 09-May-06.........1
> > 11-Jun-06..........1
> >
> > =SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006))
> > Will count the events in 2006.
> >
> > =SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006),--(MONTH(activity!A2:A1000)=7))
> > Will count the events in July 2006
> >
> > But I cannot figure out how to get it to count dates in a range - a
> > fiscal year that runs from March 31, 2005 to April 1st, 2006.
> >
> > Many thanks to any who could venture a guess!
> >


0
ardus.petus (319)
7/11/2006 5:19:05 PM
Reply:

Similar Artilces:

Function to set Date
Hi, I would like to know how to set below equation:- to set the 1-week (i.e Monday) prior to a specific date: e.g. 2009/11/24, i would like to have the output = 2009/11/16 e.g. 2009/12/31, output = 2009/12/21 -- J Hi, Try this. Cell B16 holds the specific date =B16-7-CHOOSE(WEEKDAY(B16-7,2),0,1,2,3,4,5,6) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "J" <J@discussions.microsoft.com> wrote in message news:A97DB69C-680E-4A4E-95C9-F731BE3BE9D4@microsoft.com... > Hi, > > I would like to know how to set below eq...

How do I sort activities by date in contacts?
I would like to add a date field to the activities in individual contacts and sort tasks by date. How do I do that? Right click the column headings of the activities list, choose Field = Chooser, and drag the desired field to the column headings. = Unfortunately, there is no way to make the setting apply to multiple = items. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumps...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

Show a date 30 days out
I have a feild that I would like to show what the date would be in 30 days for a feild with a date in it [Sold Date]. Sold Date feild would be 1 Jan 07 so the feild would show 30 Days later 31 Jan 07. I did =[Sold date]+30 and it did not work Thanks how about dateadd: dateadd("d", 30, [Sold Date]) -- steve. "KAnoe" <KAnoe@discussions.microsoft.com> wrote in message news:DE9BDDAE-5C84-4A52-8185-DAB6C3167E7A@microsoft.com... >I have a feild that I would like to show what the date would be in 30 days > for a feild with a date in it [Sold Date]. > ...

change date format to excel format
Hi, I have a date in 01.03.2010 format, how can I change it by using vba for every time I import the data? You can use Data>Text To Columns and use the last tab to set dates. I just recorded a macro to do it, and after tidying it left Columns("O:O").TextToColumns _ Destination:=Range("O1"), _ DataType:=xlDelimited, _ FieldInfo:=Array(1, 4) -- HTH Bob "Motta" <Motta@discussions.microsoft.com> wrote in message news:9B0F9328-704B-4F28-83A3-44BBCFF5CBBD@microsoft.com... > Hi, I have a date in 01.03...

Format Date
I have the following code in a Text Bax in a Report: =Trim("Born on" & " " & [Date of birth] & " " & ("in" & " " & [Place of Birth] & " " & Trim("and baptised this day" & " " & [Date of Baptism]))) What I would like, is to add the format command to convert the dates to dddd mmm yyyy Could anyone tell me the corrrect syntax and where this should be placed? Many thanks for any assistance. You need the format function. =Trim("Born on " & Format(...

Conditional Formatting & Dates?
This is probably a really simple question, but I can't get my head around it. In one Column I have a list of dates, and above it I have a calendar template. I want to set up conditional formatting so that if a date appears in the list, the cell of that date on the calendar is highlighted yellow. I've used: =$B$51:$B$105=DATE(2005,1,7) I put this in the cell of 7th Jan 05 (not US format) This worked great and the cell was highlighted. But when I try it with any other date after 9th Jan 2005 it fails! ie. =$B$51:$B$105=DATE(2005,2,4) - This formula to highlight on 4th Feb d...

Total column if in date range
I have a spreadsheet that has statistics on employee incidents. On column has date of each incident then columns with dollar amounts tha correspond to the types of incidents. I need to create a worksheet o subtotals by calendar quarter by type of incident. I can't seem t figure out how to sort for a date range and subtotal each type. YELP ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Yelp? Assume the dates column is in A2 going down, insert a help column B (call it...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Dsum Problem with date criteria
Hi all, Kindly solve my problem. I have 2 tables stock_received , stock_utilized. stock_received fields -- date,3pcs,4pcs,boxes. stock_utilized fields -- date,3pcs,4pcs,boxes. I have successfully made a query to get the the current stock in hand as select sum(3pcs)- dsum("3pcs","stock_utilized"), sum(4pcs)-dsum("4pcs","stock_utilized"),sum(boxes)-dsum("boxes","stock_utilized") from stock_received ; Here i have a complex problem, I cant make query with date criteria. for example 01/05/2010 to 30/05/2010 ...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Don't pull New Hire into payroll until Hire Date
Currently new employee's input into Great Plains before their actual start date need to be marked inactive or they pull into payroll. Suggest GP Payroll ignore new users until Hire date or adjust hire date has passed. - Recommended by my HR & Accounting users. ---------------- 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 the...

Date change #2
Hi, I have a cell to list when the next payment is due from a client. would like the payment dates to automatically update after the origina payment date is reached. Payments are due every two weeks. Th 'payment due' cell is G17. So if the first payment was due today, the tomorrow G17 would display the date two weeks from today. Thanks fo your help -- Message posted from http://www.ExcelForum.com Hi Assuming you have a valid date in the cell use: =G17+14 You will find some details on Date and Times in Excel on this page: http://www.ozgrid.com/Excel/ExcelDateandTimes.htm ...

how to detect that the end of a range is also end of a paragraph?
Hi, how to detect that the end of a range is also end of a paragraph? There are several ways including: Test whether the range and the last paragraph in the range have the same end eg If oRng.End = oRng.Paragraphs.Last.Range.End Then 'the range end is also a paragraph end. End If or see what the last character of the range is e.g. If oRng.Characters.Last = Chr(13) Then 'the range end is also a paragraph end End If or If oRng.Characters.Last = vbCr Then 'the range end is also a paragraph end End If -- <>>< ><<> >...

difference of dates
How can I check for entries made during the last week, what would I put in for the query. I do have a field where it stores the date. Also how would I get a report for all my data broken down into weeks? Week of 8/1 week of 8/8 etc. Thanks, Greg Blanche On Mon, 13 Aug 2007 05:56:05 -0700, GregB <GregB@discussions.microsoft.com> wrote: >How can I check for entries made during the last week, what would I put in >for the query. I do have a field where it stores the date. There are a number of date handling functions built in to Access. Just what do you mean by "the last ...

Named range links to external workbook, help!
Hi, I have a problem with making charts linking to external workbooks. Let's say I wan't to make a couple of charts in workbook "ChartBook.xls", that uses data from linked workbook "DataBook.xls". I have for example made a named range called "AHeader" defined like "='D:\Data\[DataBook.xls]Sheet2'!$A$1". If I use the named range in a field in ChartBook.xls like "=AHeader" it's fine and shows the correct text even if "DataBook.xls" is closed. But if I make a chart in "ChartBook.xls" and insert "...

Sumproduct usage?
My brain's on overload at the moment, so I'm turning to you guys for help. I need help w/ a formula that will count the number of sales that someone had based on a certain date...I thought I could use sumproduct, but I'm beginning to wonder if I was wrong about that. The relavant columns are D & M. Col D Col M 12/24/2009 Andy 12/24/2009 Charles 12/24/2009 Andy 12/25/2009 Charles You had the right function in mind. Try something like this... Use cells to hold the criteria: A1 = some date B1 = some name =SUMP...

Using sumproduct while skipping cells in the array
I'm trying to use sumproduct in a situation with two arrays of numbers. It works in the place where the arrays are contiguous, looking like sumproduct($A7:$A15,C7:C15). However, in one place one of the arrays is alternating with strings. (This format was decreed from on high, and I don't want to argue with them about it...) I thought I should be able to fake it by using sumproduct($A7:$A15>0,C7:C15>0) to ignore the strings, but Excel refused to buy that. Well, actually Excel seems to buy it but just returns 0, which is obviously wrong. I tried a bunch of options trying to...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Report on 3 Queries by Date
Hi I have 3 queries which I would like to join into one based on the servicedate/taskdate so that for any given date I can see the totals of each type of task we log for work. I would have joined these using a simple query however my issue is that not everyday I log an event in any one table (Each query based on only one table). Query1 ServiceDate CleanTimeTotal QATimeTotal ESTTimeTotal Query2 ServiceDate RepairTimeTotal Query3 TaskDate TaskTimeTotal Now I suspect I need to rename TaskDate to ServiceDate in Query3 above which I am okay to do but have no idea how to set up the...

How to auto-enter date when cell is clicked?
My spreadsheet is on a shared server, and has several columns of data entered by various people. These are columns D through L. In the far right column, I want them to just be able to click on the cell and that day's date appears. It needs to be static - not auto-update every time the file is opened. It's in Column L. The top cell is in Row 4. When they enter or change data on a row, the date they did it needs to appear in column L in that row. The point is to keep their data entry time to an ABSOLUTE minimum. I know they can select the cell and type control-;, but I'd like to e...

Calculating age in years and months from Date of Birth
I think these formulas help me a lot, but I need the answer in years an months, not decimals or days! Can you help? To calculate the number of years from the date, enter the followin formula in cell B1: =YEAR(TODAY())-YEAR(A1) The result (rounded): 23 Years For a decimal solution, use the following formula =DATEDIF(A1,TODAY(),"m")/12 The result: 23.25 Years To calculate the number of months from the date, enter the followin formula in cell D1: =DATEDIF(A1,TODAY(),"m") The result: 279 Month -- Message posted from http://www.ExcelForum.com Hi try =DATEDIF(A1,NOW(),...