Count Records

F

Freshman

Dear experts,

I've a worksheet with data in Column A to Column F. Column A is for dates
and column B is for names. I list some examples below:

12 Jan 08 Peter Ho Leung
22 Feb 08 Mary Kwan Yim
28 Mar 08 Peter Mung Ching
30 Apr 08 June Liew
3 Feb 08 June Tam Ho
13 Apr 08 Peter Lan Chi
26 May 08 Monica Cheung
27 Feb 08 Peter Cheng

Firstly, I want to count how many records in Feb 08. Secondly, I want to
count how many records with the name "Peter". I try to use the formulas below
but they don't work.

=COUNTIF(A1:A8,"*Feb*") and
=COUNTIF(B1:B8,"Peter*")

Please advise. Thanks in advance.
 
A

ASA

Excel stores its dates in numerical order from 31/12/1899 so waht you are
actually asking is for the number of dates between 39479 and 39507 (1/2/8
and 29/2/8)
so use =COUNTIF($A$1:$A$7,">39479")-COUNTIF(A1:A7,">39507") or put your two
dates in another cell and reference those.

Your Peter example should have worked, does your text have a leading space?,
You could get around it by asking for "*Peter*" but that would then find
Peter as a second name.
 
B

Bob Phillips

=COUNTIF(A:A,">="&--"2008-01-02")-COUNTIF(A:A,">="&--"2008-01-03")

=SUMPRODUCT(--(A1:A8>=--"2008-02-01"),--(A1:A8<--"2008-03-01"),--(ISNUMBER(SEARCH("Peter",B1:B8))))
 
F

Freshman

Hi ASA,

Thanks for your tips. Best regards.

ASA said:
Excel stores its dates in numerical order from 31/12/1899 so waht you are
actually asking is for the number of dates between 39479 and 39507 (1/2/8
and 29/2/8)
so use =COUNTIF($A$1:$A$7,">39479")-COUNTIF(A1:A7,">39507") or put your two
dates in another cell and reference those.

Your Peter example should have worked, does your text have a leading space?,
You could get around it by asking for "*Peter*" but that would then find
Peter as a second name.
 
F

Freshman

Thanks for your great help, Bob.

Bob Phillips said:
=COUNTIF(A:A,">="&--"2008-01-02")-COUNTIF(A:A,">="&--"2008-01-03")

=SUMPRODUCT(--(A1:A8>=--"2008-02-01"),--(A1:A8<--"2008-03-01"),--(ISNUMBER(SEARCH("Peter",B1:B8))))

--
__________________________________
HTH

Bob
 
F

Freshman

Hi Bob,

I want to learn more about the use of -- in Excel. Any good suggestions?
Thanks.
 
P

Pete_UK

Bob, it was down for me last night (well, this morning about 2:00 am)
but it was available again just before my post (I always check the
sites out first and then copy the link from the address bar in IE).

Just done it again to get your home page:

http://www.xldynamic.com/source/xld.html

So, it's working for me.

Pete
 
B

Bob Phillips

Thanks Pete, you are right. I had contacted my hosting company but got no
response as of yet, and assumed it was still down.

--
__________________________________
HTH

Bob

Bob, it was down for me last night (well, this morning about 2:00 am)
but it was available again just before my post (I always check the
sites out first and then copy the link from the address bar in IE).

Just done it again to get your home page:

http://www.xldynamic.com/source/xld.html

So, it's working for me.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top