COUNTIF function with date range

Is there anyway to use the COUNTIF function with a date 
range.  I'm trying to count each date within a particular 
month.  For example,

=COUNTIF(G5:G100, "12/**/03"

And this example with the wildcards is currently not 
working.

Thanks.  Amy
0
anonymous (74722)
5/20/2004 6:13:22 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
540 Views

Similar Articles

[PageSpeed] 5

Hi,

Try,

=SUMPRODUCT(--(MONTH(G5:G100)=12))

Hope this helps!

In article <fd8501c43e96$22a9daf0$a401280a@phx.gbl>,
 "Amy" <anonymous@discussions.microsoft.com> wrote:

> Is there anyway to use the COUNTIF function with a date 
> range.  I'm trying to count each date within a particular 
> month.  For example,
> 
> =COUNTIF(G5:G100, "12/**/03"
> 
> And this example with the wildcards is currently not 
> working.
> 
> Thanks.  Amy
0
domenic22 (716)
5/20/2004 6:26:22 PM
=SUMPRODUCT(--(MONTH(G5:G100)=12),--(YEAR(G5:G100)=2003))

or

=COUNTIF(G5:G100,">="&DATE(2003,12,1))-COUNTIF(G5:G100,">"&DATE(2003,12,31))

-- 
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


"Amy" <anonymous@discussions.microsoft.com> wrote in message
news:fd8501c43e96$22a9daf0$a401280a@phx.gbl...
> Is there anyway to use the COUNTIF function with a date
> range.  I'm trying to count each date within a particular
> month.  For example,
>
> =COUNTIF(G5:G100, "12/**/03"
>
> And this example with the wildcards is currently not
> working.
>
> Thanks.  Amy


0
terre08 (1112)
5/20/2004 6:46:37 PM
Hi

=SUMPRODUCT((MONTH(G5:G100)=12)*(YEAR(G5:G100)=2003))


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


"Amy" <anonymous@discussions.microsoft.com> wrote in message
news:fd8501c43e96$22a9daf0$a401280a@phx.gbl...
> Is there anyway to use the COUNTIF function with a date
> range.  I'm trying to count each date within a particular
> month.  For example,
>
> =COUNTIF(G5:G100, "12/**/03"
>
> And this example with the wildcards is currently not
> working.
>
> Thanks.  Amy


0
garbage (651)
5/20/2004 6:48:25 PM
There are 2 problems with this method, one is that if there are other years
with
December dates involved and if there are blanks,

=SUMPRODUCT(--(MONTH(G5:G100)=1))

will count blank cells as January dates


-- 
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-152D1E.14262220052004@nr-tor02.bellnexxia.net...
> Hi,
>
> Try,
>
> =SUMPRODUCT(--(MONTH(G5:G100)=12))
>
> Hope this helps!
>
> In article <fd8501c43e96$22a9daf0$a401280a@phx.gbl>,
>  "Amy" <anonymous@discussions.microsoft.com> wrote:
>
> > Is there anyway to use the COUNTIF function with a date
> > range.  I'm trying to count each date within a particular
> > month.  For example,
> >
> > =COUNTIF(G5:G100, "12/**/03"
> >
> > And this example with the wildcards is currently not
> > working.
> >
> > Thanks.  Amy


0
terre08 (1112)
5/20/2004 6:48:36 PM
Hi Peo,

In article <ezkCRspPEHA.620@TK2MSFTNGP10.phx.gbl>,
 "Peo Sjoblom" <terre08@mvp.org> wrote:

> if there are blanks,
> 
> =SUMPRODUCT(--(MONTH(G5:G100)=1))
> 
> will count blank cells as January dates

Didn't realize it, since I usually use both month and year.  Thanks for 
bringing this to my attention.
0
domenic22 (716)
5/20/2004 7:44:32 PM
Reply:

Similar Artilces:

Formatting a date in client report
How do I format a date field in a client side Microsoft report (rdlc)? I'm using VS2008, 9.0.30729.1 sp Windows forms project. Unformatted, the dates in my report are displayed like this: 11/19/09 00:00:00 I want to display just the date, not the zeroes. If I enter an expression in the value property for the report textbox like this: =Format(Fields!StartDate.Value, "D") (as shown in http://msdn.microsoft.com/en-us/library/ms251668.aspx) the report will display "D" instead of the formatted date. The same thing happens with other format string...

date format conversion
I have a date of 1/5/2006, it needs to be 01/05/2006. I ma having a hard time getting hte leading 0's where needed. Appreciate any help! -- Buck That sounds like a matter of FORMAT. The date itself, as 'numerical' value sounds fine. You can force a format (to display a STRING that represents a date, but *is* a string, not a date_time value) with the use of Format function: ? Format( #1/2/3# , "mm\/dd\/yyyy") 01/02/2003 Hoping it may help, Vanderghast, Access MVP "buckpeace" <buckpeace@discussions.microsoft.com> wrote in message news:01C568...

couontif function
Hi, I need a countif function for the below data. In the column C i need a formula where it will calculate the no of count of "ABC" from coulmn "A" and with condition "Manual" in column "B". Name Process Need formula ABC Manual ABC Automatic CDF Automatic ABC Manual ABC Manual DEF Manual CDE Automatic DEF Manual Radhakant.... if i were you, i would use pivot table... thanks "Radhakant Pan...

internal support function error
I am trying to send an email to a group. I switched from outlook express to microsoft outlook. It worked fine in outlook express but is not working in microsoft outlook. Below is the error I am getting. "an internal support function returned an error" Any solutions would be greatly appreciated. I am having the same error. What did you come up with? >-----Original Message----- >I am trying to send an email to a group. I switched from >outlook express to microsoft outlook. It worked fine in >outlook express but is not working in microsoft outlook. >Bel...

vlookup function to return the cell address of the found item
I have a one column list of data (around 3,000 items) - and I am using the vlookup function to determine if an item is in that list using something like the formula below: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list") I would like to know if I can have this function return the cell address or row number to indicate the location of the item in the list - is this possible? Thank you for your time and assistance You can return the (relative) row number using MATCH, like this: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list",MATCH(A1,...

COUNTIF Question #2
I need help with the COUNTIF function. What I'm trying to do is count range of cells if they are equal to the value of a specific cell Example: COUNTIF (A1:A20,�=>B1�), but it does not look at what�s in B it only counts it if the cell value is B1. Does anyone know how t accomplish this, I would greatly appreciate any help -- patrick s ----------------------------------------------------------------------- patrick s.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1469 View this thread: http://www.excelforum.com/showthread.php?threadid=26318 Hi siehe Ex...

Reduced Functionality Mode
I have Adobe InDesign but occasionally get Publisher files that someone wants me to look at or suggest changes. If I get the trial software, when it reverts to the "reduced functionality mode", will I still be able to open existing Publisher files? Will I be able to save to other formats without editing? Thanks for you help. Basically you won't be able to do much of anything once the trial period is over. In reduced-functionality mode, programs function similarly to a viewer. When a program is running in reduced-functionality mode, many menu items are unavailable (dimmed),...

some one help with the function (OR)
some one help with the function (OR) I dont know how to use this function thankyou for al -- Message posted from http://www.ExcelForum.com Did you check the help file? What part of the explanation didn't you understand? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <nader.198htq@excelforum-nospam.com>, nader <<nader.198htq@excelforum-nospam.com>> says... > some one help with the function (OR) > I dont know how to use this function > > thankyou for all...

Dynamic Range Defined by Value of Cell
I am just getting started with dynamic ranges. I have data as follows: A B C Product1 qty cost Product2 qty cost .... ProductN qty cost TOTAL qtytot costtot The number of products varies. There is other data below this that is unrelated. How can I define a range dynamically that will always capture A:C and as many rows up and including the first time it finds "TOTAL" in column A? Any help greatly appreciated. Thank you. I don't know what you mean by "define a range" b...

User defined function
I wonder if there is any way for me to write an user defined function so that everytime I open a new Excel spread sheet I can use it. Something like internal function. -- linzhang426 ------------------------------------------------------------------------ linzhang426's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27932 View this thread: http://www.excelforum.com/showthread.php?threadid=474292 Yes. If you save your UDFs in your personal.xls they will be available whenever you use Excel -- Gary''s Student "linzhang426" wrote: > &g...

Add function
Hi. I want to know how can I do to add a function for use like Max() or Min() Hi Pablo, When you create your own functions, they are referred to User Defined Functions (UDF). Some examples UDF are in in http://www.mvps.org/dmcritchie/excel/formula.htm http://www.mvps.org/dmcritchie/excel/proper.htm and instructions to install in your personal.xls http://www.mvps.org/dmcritchie/excel/getstarted.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/dmcrit...

replace function with variables
I am trying to use the replace function in an update query to eliminate text in a field; the field contains first and last names. However, sometimes the name contains an alternate name or unofficial name in parenthesis. For example the field usually contains syntax like "John Doe" but sometimes contains syntax like "William (Bill) Cherry". I need a query that will get rid of all instances of the parenthises and the name inside the parenthesis so the result is "William Cherry". I have tried several things to no avail. Any help would be appreciated. One way ...

getting value of named range
I have a named range. Lets say it goes over lots of cells. A1 to E20 Can I get the values out as a string? Single string. I have tried some things like: excel.Workbooks(MyWorkbook).Worksheets(MyWorksheet).Range(MyRange).value any ideas whoops, this should be in programming sorry "greg" <iuouh@ghfhg.com> wrote in message news:%23Ip5wsw0IHA.4164@TK2MSFTNGP03.phx.gbl... >I have a named range. Lets say it goes over lots of cells. > A1 to E20 > > Can I get the values out as a string? Single string. > > I have tried some things like: > > excel.Wo...

Date when I last change cell in a row?
Please, Help. In my database, every row represents a separate client. I wonder if it is possible to input the date when I last changed any value in a particular row. This way I will know when exactly I last updated the information for client in row 4, 5 etc. I don't need the date when the document was last saved. Please, let me know if it can be done in Excel. Thank you, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Row = 5 Then Me.Range("H1").Value = Date ...

Another Countif??
I have a spreadsheet containing various fields which is used to monito the amount of telephone calls logged by members of my department. I've used the countif function to break this down to calls logged b each individual. The next column I would like to add is the amount o calls *completed* by each individual. The completed column onl contains a true or false value. So at the moment it would read:- Name Calls Completed JOHN 5 ??? Not sure if I've explained this well but what I'm aiming for is t count Johns calls in one column(done) and in the next co...

if meet critertia then countif(a1:a10, between dates)
I hv a table of info Col A = location Col B = Due Dates if meet location, then count the number of due dates that fall in Oct or nov etc.... =SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","2005/10/31","yd")))) Col A Col B Location Due Dates USA 10/10/05 Canada 5/1/06 UK 31/12/05 Canada 2/1/06 S. America 30/10/05 Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 ...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from http://www.ExcelForum.com I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

'Accept' option not functioning in Workplace module
When users attempt to move email to the 'In Progrss' folder, they are unable to do so using the 'Accept' Action - in fatc there appears to be no way of removing email from the email without deleting it. Does someone have any insights as to why email can not be move to the 'In Progree' folder using 'Accept'? ...

Paste a range of cells in the body of an email
I have been trying to get the code to work for copying a range of cells and pasting it into the body of an email I am using excel 2007 and Lotus Notes 7.0.2 My current code works kinda sorta. It will copy and paste the text into the body of an email. But it does not open a new email it replys to a email that is in my in box. It also sends an email but it is a blank email. I would do just an attachment but the department I need to send the email to says they can not open the attachment........... Anyone have code that can do this? thanks Marie All you ever need to know ...

Smart-shape to dumb-shape? Help with cross functional flow shape?
Greetings! For my purposes, I'd like to use the cross functional shape to build up swimlanes that don't change no mater how or what kind of shape is dropped on them. Using a background page is somewhat inconvenient for my pupose. Can you tell me how to kill the code or events that cause the cross-functional shape to interact with other shapes while leaving its other behaviour intact? (in particular, I don't want other shapes to become attached to a lane nor do I want the lane to change its width) Thanks in advance! The quick solution is to go to Tools > Options > S...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

Summary Report
I have a summary report where I need a count of patients with their first visit date within a specific date range, but I also need a count of those same patients with one of four different outcomes during that same date range (delivered, miscarriage, transferred out, or no outcome withing the date range). Is this possible? Yes! Create a query that includes PatientID, FirstVisitDate and Outcome. Use the Between ... And construct in the FirstVisitDate field criteria. Click on the Sigma (looks like capital E) button in the menu at the top of the screen. Under PatientID, change ...

Excel Date Format of MMM/YY
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK Don't let the info in the Calculator mislead you. It's simply confirming that the workbook you're in is employing the 1904 Date System which is the default in Mac Excel. Dates in Mac Exce...

trying to return data with only the latest date
when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counter...

How to automaticaly populate another date
I know how to do this in excel but can't figure it out in access. I want it so that when a users inputs data into one field the date (of when he put in the data) populates another field.Please helpThanks! Hi GregThe control that the user is entering data into has an AfterUpdate event which means that you can tell your system to do "something" after the field is updated.Open the form in design view and select the field that will have data entered into it.Right click and open the properties box.In the event column select AfterUpdateClic build option (...) and select codeYou will s...