Hi malay_ko ,
Please ask your question in plain text so that everyone can see the
question. It should not be the burden of the responder to look
at a workbook to find out what the question is and then to put both
the question and the answer into words when answering.
Your question is to count the number of occurences of four different
strings in a range. Itis a worksheet related question because you
do not need a programming solution. Also Worksheet solutions
generally are not case sensitive.
You can use the COUNTIF Worksheet Function, and can read
more about that in HELP and in the following places.
The following articles are all by John Walkenbach:
COUNT, Tip 52:Cell Counting Techniques
http://www.j-walk.com/ss/excel/tips/tip52.htm
Also see Summing and Counting Using Multiple Criteria (tip 74)
http://www.j-walk.com/ss/excel/tips/tip74.htm
And an article for Microsoft Office -- Count and Sum Your Data in Excel
http://www.microsoft.com/office/using/column10.asp
and in a workbook to definitely download and maintain on your computer
Excel Function Dictionary --
http://homepage.ntlworld.com/noneley by Peter Noneley,
workbook with 157+ sheets, each with an explanation and example of an Excel function.
=COUNTIF(B$3:B$16,"1st quarter")
=COUNTIF(B$3:B$16,"2nd quarter")
=COUNTIF(B$3:B$16,"3rd quarter")
=COUNTIF(B$3:B$16,"4th quarter")
but you also have made it necessary to check that Column A is not empty
so you must check multiple criteria, which in your case should really not
be necessary but you continue things down that aren't used..
=SUMPRODUCT(($A$3:$A$16<>"")*($B$3:$B$16="1st quarter"))
=SUMPRODUCT(($A$3:$A$16<>"")*($B$3:$B$16="2nd quarter"))
=SUMPRODUCT(($A$3:$A$16<>"")*($B$3:$B$16="3rd quarter"))
=SUMPRODUCT(($A$3:$A$16<>"")*($B$3:$B$16="4th quarter"))
The actual question was:
(1st row was blank)
Date Quarter Counts
02-25-03 1st quarter
01-22-03 1st quarter
03-02-04 1st quarter
04-25-03 2nd quarter
05-25-03 2nd quarter
12-25-03 4th quarter
09-25-03 3rd quarter
05-02-02 2nd quarter
02-02-03 1st quarter
1st quarter
1st quarter
1st quarter
1st quarter
1st quarter
Solutions wanted:l
totals for 1st quarter 4
totals for 1st quarter 3
totals for 1st quarter 1
totals for 1st quarter 1
In twelve hours this thread will appear in Google Groups Archives
where you will see your question and answer. They are text only
and no access to your workbook in say a month from now. But in
any case no one can do a text search on your workbook in Google Groups.
http://google.com/[email protected]
This is where people go to find previously asked/answered questions
form newsgroups.i
Read about newsgroups here:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
Read about posting to *Excel* newsgroups (our rules of netiquette) here:
http://www.cpearson.com/excel/newposte.htm
http://www.mvps.org/dmcritchie/excel/posting.htm
BTW, your attachment stays at Excel Tips so the attachment itself
is not a burden on servers and personal disk storage, and many
people will simply ignore your entire post for several reasons but
the main one is that you did not include the question in your posting.
