Total number of Jan 2007 entries in a list of date formatted field

I have a report that captures work done by sales reps.  For each entry there 
exists a date.  This report is exported in Excel format and the date is 
captured as mm/dd/yyyy hh:mm.  I would like to have a column that lists the 
number of work entries by Jan 2007, Feb 2007, Mar 2007, etc.

I am at a loss as to how I can conditionally count the occurrence based on 
the month and the year in this date field.

Thanks in advance.
0
BrianV11 (2)
8/21/2007 7:48:04 PM
excel 39880 articles. 2 followers. Follow

2 Replies
237 Views

Similar Articles

[PageSpeed] 9

=SUMPRODUCT(--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))

where B2:B200 holds the dates


you can expand on that if you want to count for an individual


=SUMPRODUCT(--(A2:A200="Joe"),--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))

where it would count Joe's Jan 2007 sales

-- 
Regards,

Peo Sjoblom



"BrianV11" <BrianV11@discussions.microsoft.com> wrote in message 
news:BF09CBD7-1F16-4408-A816-C2899D2C6CB7@microsoft.com...
>I have a report that captures work done by sales reps.  For each entry 
>there
> exists a date.  This report is exported in Excel format and the date is
> captured as mm/dd/yyyy hh:mm.  I would like to have a column that lists 
> the
> number of work entries by Jan 2007, Feb 2007, Mar 2007, etc.
>
> I am at a loss as to how I can conditionally count the occurrence based on
> the month and the year in this date field.
>
> Thanks in advance. 


0
terre081 (3244)
8/21/2007 8:06:12 PM
Thanks.  That is EXACTLY what I needed.

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))
> 
> where B2:B200 holds the dates
> 
> 
> you can expand on that if you want to count for an individual
> 
> 
> =SUMPRODUCT(--(A2:A200="Joe"),--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))
> 
> where it would count Joe's Jan 2007 sales
> 
> -- 
> Regards,
> 
> Peo Sjoblom
> 
> 
> 
> "BrianV11" <BrianV11@discussions.microsoft.com> wrote in message 
> news:BF09CBD7-1F16-4408-A816-C2899D2C6CB7@microsoft.com...
> >I have a report that captures work done by sales reps.  For each entry 
> >there
> > exists a date.  This report is exported in Excel format and the date is
> > captured as mm/dd/yyyy hh:mm.  I would like to have a column that lists 
> > the
> > number of work entries by Jan 2007, Feb 2007, Mar 2007, etc.
> >
> > I am at a loss as to how I can conditionally count the occurrence based on
> > the month and the year in this date field.
> >
> > Thanks in advance. 
> 
> 
> 
0
BrianV11 (2)
8/22/2007 12:10:00 PM
Reply:

Similar Artilces:

*.csv Format Saving Problem
Hello: New at this. I have an Excel spreadsheet that I wish to save in .csv format. It never saves the formatting like Underlines, Bolds, etc., no matter what I do. This is the first time I've played with .csv Is there some reason why, with this format, these Bolds and Underlines can't be saved ? If so, why, and what else is effected ? Thanks, Bob By definition, Comma Separated Value files contain only values, not formatting. In article <aOednUD-bNWQUgDenZ2dnUVZ_sGdnZ2d@comcast.com>, "Robert11" <rgsros@notme.com> wrote: > New at this. > >...

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

2003 to 2007 02-25-08
I created a db on home computer in v. 2003. Works fine opening it in 2003 on computer at work. Then try to work with it in 2007 and every time I try to see anything in Design view I get this error: The command or action 'OpenTable' isn't available now. *You may be in a read-only database or an unconverted databse from an earlier version of MS Access. *The type of object the action applies to isn't currently selected or isn't in the active view. Use only those commands and macro actions that are currently available for this database. I've tried: ...

So many Tunnel Adapters
Hi, I understand that Tunnel Adapter LAN is for encapsulating IPv6 packets with an IPv4 header so that they can be sent across an IPv4 network. Few queries popped up in my mind based on this :- 1. Why is the numbering for the Tunnel adapter LAN not sequential ? It is like 6,7,12,13,14,15,16. A strange numbering scheme ?! I tried to figure it out by thinking of some arithmetic series. But, it does not seem to fit in. There is a huge gap between 7 and 12. Any ideas ? 2. What is the need for so many number of Tunnel Adapter LAN connection ? Can you tell me a scenario that re...

Irregular formation of numbers
Hi, I hope someone can help, I am trying to format numbers in a column. the numbers are all 10 digits (ie. 0405613020) and from cell to cel are all different. What I am trying to do is for them to take on the format below: 0405.61.30.20 I have tried different things.. but nothing seems to work.. I have ove 250 cells that need to look like that, and it's very long and tedious.. Hope someone can help me.. Diamonelle :confused -- Message posted from http://www.ExcelForum.com Hi You can put this formula in a helper column, copy it down as far a needed and then copy-paste special -...

Removing duplicate records with long names in Excel 2007
Greetings, This is probably a very simple question, sorry. I have annual lists of company names and am trying to make a report of changes in the lists; companies added and companies dropped. The problem is that the names in the register are long and have lots of abbreviations which might differ from year to year; one extra dot, a space missing etc., thus, making the records for the same company differ from year to year. Now, how can I filter two lists to remove the same records? Is there a function to do that? Generally, for the same company, the first two words in the name r...

Formating Dates
I need to update a list of dates to show all days as 01. For example, where date shows 03/28/2009, I need it to read 03/01/2009. I appreciate any guidance! -- LL28 On Thu, 6 May 2010 12:12:01 -0700, LovingLife28 <LovingLife28@discussions.microsoft.com> wrote: >I need to update a list of dates to show all days as 01. For example, where >date shows 03/28/2009, I need it to read 03/01/2009. I appreciate any >guidance! If you want to convert the dates to the first of the month, then, with your date in A1: =A1-DAY(A1)+1 If you want to leave the date a...

Accessing exchange 2007 rooms from outlook 2000/2003
Does anyone have a link to a guide on how this work. Previous in e2k we used pub. folders as our meeting rooms calender. But now i want to use the rooms in e2k7, but i cannot access a room or i dont know where to find it from outlook. /Ren´┐Ż Talk to your Exchange administrator. EX2007 may have been installed with no public folders. It has extensive features for setting up resources as individual mailboxes. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming: Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx...

Date format on a drop down box
I have a dropdown box that is linked to a cell where a date is put that is used for other things. The drop down box is filling itself from a range, which contains a sequential list of dates. I have verified that the dates are indeed dates and not text values. However, whenever I select a date using the drop down list, the link cell reverts to a serial format. I have tried to reformat the cell to be mm/dd/yyyy manually but without any success. Should be a simple reason but I am stumped here. Hi You have to format the cell' where you select the date, too. Formatting the data va...

Is there a maximum number of users that can share an Outlook calen
I have 4 users sharing my Outlook calendar, I cannot see how to remove users, and wondered if there was a maximum as I cannot seem to add more users? Are you using Delegates or the permissions tab on the properties dialog? If there is a real limit, its well above 4 - I know of sites where thousands have permission to view a calendar. There is a practical limit for managing individual users but this can be worked around by giving permission to security groups. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cent...

Limiting a drop down list to a value in another field
Hello, I have an Access database which lists certain software. There are two fields; the software company, i.e. Microsoft, and then the name of the software itself,i.e. Access. I want the name of the software to be limited by the company name, so the second drop down would not list, say, Adobe Acrobat. Is there a way to do this? Many thanks in advance. Joanne wrote: >I have an Access database which lists certain software. There are two >fields; the software company, i.e. Microsoft, and then the name of the >software itself,i.e. Access. I want the name of the softwa...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

date error
hi i have 2 problem. 1) iam using a vlookup formula, but when there is no value in the cell the cell shows the ( 0 for date and jan for month) 0-jan. but i want to be 0 instead of the 0-jan, why its happening, 2) i have a database upto 2000 subscriber paying monthly sub. some customer pays twice in month. i used vlookup formula . it shows only one date , is there possible to show all the date in one cell, so i can know how many times that customer paid the subs in these month. if instead if vlookup i used the sumif formula its add the date and i got the wrong date. so pls help. rega...

Outlook 2007 + Lotus domino
Hi, I use a lotus domino r5 server And i would like to connect my outlook 2007 client to my domino server. Could you say me if it's possible ? And how ? Thanks You'll need to get the Notes connector from your administrator. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup by ...

Custom Number Format 04-12-10
I know nothing about using custom number format, and need to undersand when and how to use it. Any link to recommend? Thanks in advance. Regards, Emece.- This Microsoft link expains the concepts fairly well http://office.microsoft.com/en-us/excel/HP051995001033.aspx -- If this helps, please remember to click yes. "Emece" wrote: > I know nothing about using custom number format, and need to undersand when > and how to use it. Any link to recommend? > > Thanks in advance. > > Regards, > Emece.- ...

Adding LDAP entries to the Global Address List
I have two exchange 2003 sp1 servers in a a front end and back end configuration running on windows 2003 sp?. I'm running window 2003 ADS. My organization exist in a bigger network environment that uses LDAP server for directory services. My questions, is there a way that I can include the LDAP server information along with the Global Address List. Sort of like configuring a mail clients to pull information from an LDAP server. Is this even possible? "thutchin" <thutchin@discussions.microsoft.com> wrote: >I have two exchange 2003 sp1 servers in a a front end...

Closing Database in 2007
I use the macro, "Close Database" attached to a command button on my switchboard which works fine. However, it does not close Access completely, it leaves the getting started page open. Please could someone let me know how I can close the database and Access in one go. Thanks Try the Quit action. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nigel" <Nigel@discussions.microsoft.com> wrote in message news:8F21475C-931C-...

Change the assigned chart number
If you use the Macro Recorder and select several charts (in the same worksheet) in secession you can see that Excel has assigned a name to each chart such as Chart 1, Chart 2, ect.. Is there a way to rename say Chart 15 to Chart 3, assuming Chart 3 does not already exist? -- Al Eaton aleaton@bak.rr.com To manually rename a chart object: Hold the Ctrl key, and click on the chart to select it Click in the Name box, to the left of the formula bar Type a new name, or edit the existing name Press the Enter key To programmatically rename a chart object: ActiveSheet.ChartObjects("Ch...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

Page numbers
I'm using Office 2000 Professional. Is there a way to include page numbers by default instead of having to insert them every time I create a new document? This newsgroup supports Microsoft Access, the relational database. I'm not sure how to interpret your question... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as ...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

deleting a fields content
I have a situation in which a user will need to delete only the content of one field for a record. For each record in the table there is a column (field) called IsChildOf. On the webpage there will be a button that will delete only the content (a number) of the field IsChildOf for the record. So, if record ID 449945 has 25 in the IsChildOf and the person uses the delete button, the 25 is deleted. How is the DELETE statement constructed for something like that (I'm a noob to sql)? Thanks. John John > On the webpage there will be a button that will > delete onl...

ver 2007 will not open
When I click on the Word shortcut the cursor spins and nothing happens. When I click on an existing Word document I get the error "Windows can not find 'C:\Users\... filename.docx'. Make sure you typed the name correctly, and then try again." Things I've tried: Logging on with a different user account The Microsoft "Fix It" tool Starting in Safe Mode Using the "docx.txt and renaming to .reg file" Changing the default printer .... but nothing has worked. Other Info: OS: Vista Home Premium SP1 Microsoft Office 2007 Student & Home...

Installing News Reader in Outlook Office 2007
I recently purchased a new HP computer with the Windows 7 64-bit operating system on which I installed Office 2007. I want to activate the News Reader within Outlook. The Help file tells me 1. On the Standard, click the Toolbar Options arrow . 2. Point to Add or Remove Buttons, and then click Customize. 3. Click the Commands tab. 4. In the Categories list, click Go. 5. In the Commands list, click News, and without releasing the mouse button, do one of the following: * Drag the News command onto the Outlook menu bar, and then release the mouse button. ...

Invoice Numbers
First let my apologies. I posted a question earlier but I can't find we're I posted it! I would like to know if there's a way to auto number an invoice before it's printed? I would think a new table would be required but I'm really not sure how to setup what's required. Thank you in advance. Don Don, It depends on how far in the past you want to generate the Invoice number before it is printed AND the steps you go thru to print the Invoice but I can give you a yes, it can be done. However, more information required to tell how? -- Gina ...