|
Count Records
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. |
Count Records
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. "Freshman" wrote: 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. |
Count Records
=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 "Freshman" wrote in message ... 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. |
Count Records
Hi ASA,
Thanks for your tips. Best regards. "ASA" wrote: 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. "Freshman" wrote: 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. |
Count Records
Thanks for your great help, Bob.
"Bob Phillips" wrote: =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 "Freshman" wrote in message ... 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. |
Count Records
Hi Bob,
I want to learn more about the use of -- in Excel. Any good suggestions? Thanks. "Bob Phillips" wrote: =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 "Freshman" wrote in message ... 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. |
Count Records
Have a look at Bob's own site:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html where he gives a thorough explanation of SP and --. Hope this helps. Pete On Jun 18, 10:10*am, Freshman wrote: Hi Bob, I want to learn more about the use of -- in Excel. Any good suggestions? Thanks. "Bob Phillips" wrote: =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 "Freshman" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
Count Records
Unfortunately it is down at the moment.
-- __________________________________ HTH Bob "Pete_UK" wrote in message ... Have a look at Bob's own site: http://www.xldynamic.com/source/xld.SUMPRODUCT.html where he gives a thorough explanation of SP and --. Hope this helps. Pete On Jun 18, 10:10 am, Freshman wrote: Hi Bob, I want to learn more about the use of -- in Excel. Any good suggestions? Thanks. "Bob Phillips" wrote: =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 "Freshman" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
Count Records
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 On Jun 18, 11:38*am, "Bob Phillips" wrote: Unfortunately it is down at the moment. -- __________________________________ HTH Bob "Pete_UK" wrote in message ... Have a look at Bob's own site: http://www.xldynamic.com/source/xld.SUMPRODUCT.html where he gives a thorough explanation of SP and --. Hope this helps. Pete On Jun 18, 10:10 am, Freshman wrote: Hi Bob, I want to learn more about the use of -- in Excel. Any good suggestions? Thanks. "Bob Phillips" wrote: =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 "Freshman" wrote in message ... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Count Records
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 "Pete_UK" wrote in message ... 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 On Jun 18, 11:38 am, "Bob Phillips" wrote: Unfortunately it is down at the moment. -- __________________________________ HTH Bob "Pete_UK" wrote in message ... Have a look at Bob's own site: http://www.xldynamic.com/source/xld.SUMPRODUCT.html where he gives a thorough explanation of SP and --. Hope this helps. Pete On Jun 18, 10:10 am, Freshman wrote: Hi Bob, I want to learn more about the use of -- in Excel. Any good suggestions? Thanks. "Bob Phillips" wrote: =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 "Freshman" wrote in message ... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Count Records
Thanks Pete for your recommendation. Best regards.
"Pete_UK" wrote: Have a look at Bob's own site: http://www.xldynamic.com/source/xld.SUMPRODUCT.html where he gives a thorough explanation of SP and --. Hope this helps. Pete On Jun 18, 10:10 am, Freshman wrote: Hi Bob, I want to learn more about the use of -- in Excel. Any good suggestions? Thanks. "Bob Phillips" wrote: =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 "Freshman" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
Count Records
You're welcome - thanks for taking the trouble to feed back.
Pete On Jun 19, 2:48*am, Freshman wrote: Thanks Pete for your recommendation. Best regards. |
| All times are GMT +1. The time now is 09:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com