Help, Excel Formula Needed -- Urgent

[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???????

 

[Bob Phillips]

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

--

HTH

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

 

[Ken Wright]

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 D150. Now in E1 put

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

Copy cell E1 and paste into E2:E50