Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 14th 04, 10:21 PM
Urgent
 
Posts: n/a
Default 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   Report Post  
Old December 14th 04, 10:27 PM
Bob Phillips
 
Posts: n/a
Default

=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   Report Post  
Old December 14th 04, 10:32 PM
Ken Wright
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I set up a formula in excel? wiskeyjruiz New Users to Excel 5 January 16th 05 07:00 PM
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM
Converting an Excel formula to an Access query formula Adam Excel Discussion (Misc queries) 1 December 15th 04 03:38 AM
Suddenly Excel can't calculate formula!!! Bob H Excel Worksheet Functions 2 November 30th 04 08:35 PM
Excel 2003 - Formula result shows as 0:00 Serena Excel Worksheet Functions 4 November 11th 04 10:18 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017