Help, Excel Formula Needed -- Urgent
 

Hi have a column that contains information like 2W, 2E, 3W, 3W and so on.
How can I create a formula that will give me the total number of 2W or 3W in
the column???????

=COUNTIF(A:A,"2W")
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Urgent" wrote in message
...
Hi have a column that contains information like 2W, 2E, 3W, 3W and so on.
How can I create a formula that will give me the total number of 2W or 3W
in
the column???????

Do you really need a formula, or will another solution do?

Give your column a header, eg CODE
Select all your data from header to end of data and then do Data / Pivot table
and Chart Report, then just hit Next / Next / Finish.
Now drag the CODE field from the list to where it says ROW FIELDS, and then also
drag it again from the list into where it says DATA.

You should now have a list of unique codes with their respective counts.

IF you really want a formula then get yourself a list of unique codes, (Data /
Filter / Advanced filter with 'Unique entries' and 'copy to another location'
will do that) and then use =COUNTIF in conjunction with that list.

Example, assuming your data is in A1:A10000 - Get a unique list of codes and put
it in say D1:D50. Now in E1 put

=COUNTIF($A$1:$A$10000,D1)

Copy cell E1 and paste into E2:E50

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Urgent" wrote in message
...
Hi have a column that contains information like 2W, 2E, 3W, 3W and so on.
How can I create a formula that will give me the total number of 2W or 3W in
the column???????


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004