Remember Me?

#1
December 14th 04, 10:21 PM
 Urgent Posts: n/a
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???????

#2
December 14th 04, 10:27 PM
 Bob Phillips Posts: n/a

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

#3
December 14th 04, 10:32 PM
 Ken Wright Posts: n/a

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

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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post wiskeyjruiz New Users to Excel 5 January 16th 05 07:00 PM Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM Adam Excel Discussion (Misc queries) 1 December 15th 04 03:38 AM Bob H Excel Worksheet Functions 2 November 30th 04 08:35 PM Serena Excel Worksheet Functions 4 November 11th 04 10:18 PM

All times are GMT +1. The time now is 04:54 AM.