Countif and dates???

I have a 20,000 entry spreadsheet that contains dates in this format 
07/04/2010, As you can imagine there are quite a few of these entries 
for each month.  I want to count the number of them for a particular 
month.  I can't seem to use wildcards for some reason and cant find the 
answer why on the net.

There formula that I would expect to work goes like this: - (ps I am 
looking for all the March(03) entries in the column A)
=COUNTIF(A:A, "*/03/8")

This always returns a 0 and I can't see why?

In an ideal would I would like this to count the above AND also 
countains the word EXAMPLE in the F:.  I think the second part is much 
easier than the first as I can easily use wildcards for searching for 
things on their own.

Any help would be greatly appreciated as I am confused.com!

Scott
0
Scott
4/7/2010 7:30:34 PM
excel 39879 articles. 2 followers. Follow

7 Replies
553 Views

Similar Articles

[PageSpeed] 36

maybe this. You may NOT use the entire column and shouldn't for other 
formulas.
=sumproduct((year(a2:a22)=2003)*(month(a2:a22)=3)*(f2:f22="example"))

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Scott" <hot.cakes7@virgin.net> wrote in message 
news:t35vn.47423$Mq2.13672@newsfe30.ams2...
>I have a 20,000 entry spreadsheet that contains dates in this format 
>07/04/2010, As you can imagine there are quite a few of these entries for 
>each month.  I want to count the number of them for a particular month.  I 
>can't seem to use wildcards for some reason and cant find the answer why on 
>the net.
>
> There formula that I would expect to work goes like this: - (ps I am 
> looking for all the March(03) entries in the column A)
> =COUNTIF(A:A, "*/03/8")
>
> This always returns a 0 and I can't see why?
>
> In an ideal would I would like this to count the above AND also countains 
> the word EXAMPLE in the F:.  I think the second part is much easier than 
> the first as I can easily use wildcards for searching for things on their 
> own.
>
> Any help would be greatly appreciated as I am confused.com!
>
> Scott 

0
Don
4/7/2010 7:45:30 PM
One way:

=sumproduct(--(text(a1:a10,"yyyymm")="201003"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+). 

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Another way is to count the number of dates before the next month and subtract
the number of dates before the month you want:

=countif(a:a,"<"&date(2010,4,1)) - countif(a:a,"<"&date(2010,3,1))

(=countif() can use entire columns in any version of excel)

Scott wrote:
> 
> I have a 20,000 entry spreadsheet that contains dates in this format
> 07/04/2010, As you can imagine there are quite a few of these entries
> for each month.  I want to count the number of them for a particular
> month.  I can't seem to use wildcards for some reason and cant find the
> answer why on the net.
> 
> There formula that I would expect to work goes like this: - (ps I am
> looking for all the March(03) entries in the column A)
> =COUNTIF(A:A, "*/03/8")
> 
> This always returns a 0 and I can't see why?
> 
> In an ideal would I would like this to count the above AND also
> countains the word EXAMPLE in the F:.  I think the second part is much
> easier than the first as I can easily use wildcards for searching for
> things on their own.
> 
> Any help would be greatly appreciated as I am confused.com!
> 
> Scott

-- 

Dave Peterson
0
Dave
4/7/2010 7:51:35 PM
Thanks Don,

point taken on using the entire column.

the first part works a treat but how about a wildcard for the example 
part at the end?  For instance human error means that people may 
accidentally put just "exam" in or contain a rogue.(fullstop) or 
something in the cell.  So maybe I need to change this so that it is a 
cell contain "exam" somewhere in the cell?

Many thanks!

Scott

On 07/04/2010 20:45, Don Guillett wrote:
>
> maybe this. You may NOT use the entire column and shouldn't for other
> formulas.
> =sumproduct((year(a2:a22)=2003)*(month(a2:a22)=3)*(f2:f22="example"))
>

0
Scott
4/7/2010 8:06:28 PM
try ="exam*"

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Scott" <hot.cakes7@virgin.net> wrote in message 
news:aB5vn.140317$Vh1.8025@newsfe15.ams2...
> Thanks Don,
>
> point taken on using the entire column.
>
> the first part works a treat but how about a wildcard for the example part 
> at the end?  For instance human error means that people may accidentally 
> put just "exam" in or contain a rogue.(fullstop) or something in the cell. 
> So maybe I need to change this so that it is a cell contain "exam" 
> somewhere in the cell?
>
> Many thanks!
>
> Scott
>
> On 07/04/2010 20:45, Don Guillett wrote:
>>
>> maybe this. You may NOT use the entire column and shouldn't for other
>> formulas.
>> =sumproduct((year(a2:a22)=2003)*(month(a2:a22)=3)*(f2:f22="example"))
>>
> 

0
Don
4/7/2010 9:01:30 PM
No that does not work?

On 07/04/2010 22:01, Don Guillett wrote:
> try ="exam*"
>

0
Scott
4/7/2010 9:33:48 PM
)*(left(f2:f22,4)="exam"))

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Scott" <hot.cakes7@virgin.net> wrote in message 
news:%S6vn.381632$2R.211969@newsfe11.ams2...
> No that does not work?
>
> On 07/04/2010 22:01, Don Guillett wrote:
>> try ="exam*"
>>
> 

0
Don
4/7/2010 10:13:05 PM
oops.  I missed the Example in column F stuff.

if the cell in column F contains Example (and nothing more):
=sumproduct(--(text(a1:a10,"yyyymm")="201003"), 
            --(f1:f10="example"))

if the cell in column F starts with Example and may have more stuff in the cell:
=sumproduct(--(text(a1:a10,"yyyymm")="201003"), 
            --(left(f1:f10,7)="example"))

if the cell in column F can have example anywhere:

=sumproduct(--(text(a1:a10,"yyyymm")="201003"), 
            --(isnumber(search("example",f1:f10))))

(Use =find() instead of =search() if you want it to be case sensitive)







Dave Peterson wrote:
> 
> One way:
> 
> =sumproduct(--(text(a1:a10,"yyyymm")="201003"))
> 
> Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
> 
> =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> to 1's and 0's.
> 
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> 
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
> 
> Another way is to count the number of dates before the next month and subtract
> the number of dates before the month you want:
> 
> =countif(a:a,"<"&date(2010,4,1)) - countif(a:a,"<"&date(2010,3,1))
> 
> (=countif() can use entire columns in any version of excel)
> 
> Scott wrote:
> >
> > I have a 20,000 entry spreadsheet that contains dates in this format
> > 07/04/2010, As you can imagine there are quite a few of these entries
> > for each month.  I want to count the number of them for a particular
> > month.  I can't seem to use wildcards for some reason and cant find the
> > answer why on the net.
> >
> > There formula that I would expect to work goes like this: - (ps I am
> > looking for all the March(03) entries in the column A)
> > =COUNTIF(A:A, "*/03/8")
> >
> > This always returns a 0 and I can't see why?
> >
> > In an ideal would I would like this to count the above AND also
> > countains the word EXAMPLE in the F:.  I think the second part is much
> > easier than the first as I can easily use wildcards for searching for
> > things on their own.
> >
> > Any help would be greatly appreciated as I am confused.com!
> >
> > Scott
> 
> --
> 
> Dave Peterson

-- 

Dave Peterson
0
Dave
4/7/2010 10:18:57 PM
Reply:

Similar Artilces:

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Find/Replace Date Question
Using Excel 2003. Problem replacing a series of dates having a date format of dd/mm/yy. I want to replace all dates ending with /05 with /06. I enter in the Find box /05 and in the Replace box /06 Error is "Microsoft Office cannot find a match" I never had this problem with earlier versions of Excel. TIA Jim If those are real dates, try replacing 2005 with 2006. Jim wrote: > > Using Excel 2003. > > Problem replacing a series of dates having a date format of dd/mm/yy. I want > to replace all dates ending with /05 with /06. I enter in the Find box /05 > ...

Account Owner last Changed (or) Last modified date
is there any field in the CRM Database is capturing Account Owner last modified date. check "modifiedon" and "modifiedby" fields which capture the last modification to the record and are present in most of the entities. "srini" wrote: > is there any field in the CRM Database is capturing Account Owner last > modified date. > > Amit, those two fields are going to be used only if there are any changes in the entity Information. my question is is there any field, rather is there any way to capture Account owner last modified (or) changed. ex:Us...

Address book not up to date
Hello, As we are updating Active Directory and Exchange, our users have several more days their address book not up to date. I rebuild the Recipient Update Service but nothing changes. Bonjour, Alors que nous faisons des mise � jour dans Active Directory et Exchange, nos utilisateurs se retrouvent plusieurs jours avec leur carnet d'adresse pas actualis�. Je reconstruit la mise � jour des destinataires, mais rien ne change. Is there another place where I can set it ?? Thank you in advance, Cyril It happens when you create a new user and you cannot find his name in the "global ...

Date: Today Date: Yesterday etc.
Is there a way to remove these titles in Outlook beta 2007? I already have a column with the date of email, I'd like to remove the ones in the subject line. Is there is a way? TIA.........katy the same way you could in outlook 2003 - by disabling group by. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exc...

If the date is more than 6 weeks ago....
Can someone please tell me the expression for the following? I have 2 date fields [Date Issued] and [Response Received] Using conditional formatting in a report, i want [RESPONSE RECEIVED] to turn yellow if it's null and [Date Issued] was more than 6 weeks ago. All help much appreciated, as always! Kirstie [RESPONSE RECEIVED] Is Null And [Date Issued] < DateAdd("w",-6,Date()) PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "Kirstie Adam" <kirstiea@ecosse.net(nospam)> wrote i...

contracts with no end date
Hello, We have hosting contracts with our clients that are on a month-to-month basis until the client cancels. Since a contract requires an end date, I am wondering how other people are dealing with this. Do you simply set the contract to expire way in the future? I was thinking that I would simply change the requirement on the expireson attribute, but Microsoft marked it read-only and it is a system required field. Thanks, Shan McArthur ...

Date Time Picker construction problem
Hi, I'm wanting to implement a dialog with a DateTimePicker. I have included mscorlib.dll in the following manner - #using <mscorlib.dll> When trying to compile I get the error message error C"501:'CClass:DateTimePicker':missing storage-class or type specifiers Is there anything else I need to include to get this to work? TIA Tony Are you refering to CDateTimeCtrl? I have never heard of CClass:DateTimePicker! AliR. "Tony" <lazyherbert@homeandresting.com> wrote in message news:OzMJ$fDzGHA.4580@TK2MSFTNGP05.phx.gbl... > Hi, > > I'...

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 $0.00 2 08/13/2006 1 $10.00 3 08/14/2006 1 $20.00 4 08/01/2006 0 $0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

Need help converting "InstalledOn" date for all Win32_QuickFixEngineering entries
Hello, all. I'm new to Powershell scripting, but have used VBScript for years. I've found that there is no way in VBScript to do the following and am hoping someone can help with a PowerShell script for the following: I need to be able to document all of the HotFixIDs, their description, and the date that the HotFixes were installed. I want this list to be a CSV list available in the root directory of the computers that need this information. All of the computers that I'm going to use this on are 64-bit computers and are running Windows Server 2008 and Vista. I found ...

Not So Long Date Format
Hi, I have a customer that wants to see dates printed on her reports as: October 8, 2007. When I look at the pre-defined date formats, I see Medium Date which is 08-OCT-07 and I see LongDate which will give me: Monday, October 8, 2007. How do I get the format for which my customer is asking. The report has a lot of dates in many different places, so I am hoping that I don't have to Right(x) the string every single time. Hopefully there is a better way. Any help appreciated J Joe <delphi561@cox.net> wrote in news:1191894599.877330.210770@d55g2000hsg.g...

Make User Date update on 24/7 Great Plains session.
I'm using Great Plains 8. I have a 24/7 SOP Great Plains process running on the server. The orders imported use the 'User Date" to set the imported order's 'Order Date' value. The User Date does not increment on it's own. Can anyone tell me a way to update the User Date on this un-attended Great Plains session? Thanks! Kevin R How about a GP macro that changes the user date and then add that macro to run using Windows Scheduled Tasks? Never tried it but should work. "Kevin R" wrote: > I'm using Great Plains 8. I have a 24/7 SOP Gr...

Select a range based on system date
Part 1 I have a spreadsheet that I want to open protected (locked). However, I want to unlock a specific range for data entry based on the day of the actual system date. For example if today is 11/5/08 then we want to unlock row 5, specifically cells B5:I5. I have experimented with several functions, which return the desired results, but I am not skilled enough to place them into a well-written VBA subroutine. Please note the following function examples that return the start and ending cells of the desired range. =ADDRESS(DAY(NOW()),2) this will display the start cell of the range (B5) an...

Countif
I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

Converting a date from serial back to mm/dd/yyyy hh:mm:ss
I have a spreadsheet that insists on only showing the serial value of the date. For example the cell displays as "6/15/2005 9:58:08 AM" but in the formula bar lists itself as "6/15/2005 9:58:08 AM". If I try to go to Format-->Cells and set the formatting to a different date setting it does not change how it appears and prints inside of the spreadsheet. Anybody ever run into this problem? i think in Excel 2007 there's a DATEVALUE function that would allow you to convert any date formatted as text to be converted back to a serial number reckon by Excel as dates...

Text To Dates
I have trouble with Excel converting text to dates. For example: I set the row to text and enter Jan 02, Feb 02 ... Dec 02; I copy the entries Jan 02 through Dec 02 and paste them in the same row (Which I previously set to text); I replace the 02 with 03 expecting to have Jan 03, Feb 03 ... Dec 03; However, Excel, in its infinite wisdom, converts my new items to 3 Jan 04, 3 Feb 04 ... 3 Dec 04. How can I get Excel to accept my change and keep the fields as Jan 03 etc. An easy fix is to use the month fill, but I have had similar problems in the past and wondered if what I can do to fix...

Query for previous week, Sun thru Sat, from date field
I run a query for distribution via macro. I could use the 'between dates' criteria, but is there a better way? I have code 'Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)' that will pull data for the previous month without using 'between dates'. Can it be modified for previous week? -- LMR Between DateAdd("d",1-WeekDay(Date())-7,Date()) And DateAdd("d",1-WeekDay(Date())-1,Date()) For today's date (April 9, 2010 that should return Between #3/28/2010# and #4/3/2010# On Ap...

Check issue date vs. Posting Date Problem
I have just noticed that when importing my check transactions from my bank that it changes my Draft Date to the date the check was posted/cashed. Why all of sudden is this happening. Please EMAIL a reply This is an option you can turn off in Money 2004. Here's how... Go to Tools/Options and select the "Online Services" tab. Deselect the "Overwrite transaction dates with dates from from downloaded transactions". This may not help if you are using a different version but I thought I would pass it along. Andy Nestor "Steve" <mandyspizza@comcast.net...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

WFM working off a date
I was wondering if anyone has ideas about triggering a workflow based on a date. I would like to auto generate a message to go out 3 days prior to an estimated date to close an opportunity, but so far had no success. I'm willing to bet that I am not the first to want to use a date as a trigger... Has anyone out there come up with any workarounds for this silly ommision in the current product? I would hope, and please confirm if known, that this is addressed in the newest MSCRM version. Thanks for your input, Scott ...

CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...

converting numbers to date format
Hello, I have 3 columns in a worksheet that contain a number that represents the date and the other column has a number that represents the month and the last is the year as a single digit. (A2=14, B2=12, C2=9) The 3 columns are formated number with 0 decimal. I have a 4th column that is formated date. (3/14/01). In this 4th column i have =B2 & "/" & A2 & "/" & C2. The result is 14/12/9 and does not seem to be a date format. I suspect it is a string. What do I have to do to convert this to date? Cheers! Make sure the cell with the fo...

Look up by Date issue
I have a date field that I want to filter based on a date. However the date contains the date and time. I want to allow the user to enter the date to filter the date. When we enter the date in the criteria - it comes up with no records. However there is data when not filtered. What can I do? -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 On Tue, 09 Mar 2010 21:55:53 GMT, "mattc66 via AccessMonster.com" <u16013@uwe> wrote: >I have a date field that I ...