Count If Problem

[jereece]

On Tab 1 In column A I have a list of Event Codes that are exported from a mainframe database for plant events. Each row representes an event and may have several different event codes separated by a comma. For example in cell A1, I may have "A2b, C1c, P3f", cell A2 may have "V3n, E3d". Some cells will have multiple codes and some will have one.

On Tab 2 of the spreadsheet, I have listed all the codes in column A. In Column B I want to count the number of times the code in the adjacent cell (column A) occurrs in Tab 1 Column A.

From these counts I will create graphs.

My problem is in using "count if" because it won't count if there is more than one code.

I have attached a small example of what I am trying to do. Any help is appreciated.

Jim

 

[GohDiamond]

In Cell B2 of the Count worksheet (assuming that Cell A1 and B1 are used for headers) enter the following formula
=COUNTIF(Data!$A$2:$A$5,"*"&A2&"*")

You can append a wildcard before and after the relative cell on the count page.

"*"&[Cell Ref]&"*"

You can make the first argument a permanent range on the Data Worksheet
Data!$A$2:$A$5 ($ creates a fixed reference)

A fixed reference allows you to drag the formula down the column with a relative reference for the Rows on the Count worksheet.

Excel was my first love, ;-)

Goh

 

[jereece]

Thanks for the help. The only problem with this is counting sub codes for major codes. For example, lets say in one cell I have (P2a, H4b, P3t, A2b). On my counts tab, in the cell that counts "P" (a major code), it also counts P2a and P3t as just P. This is because of the wild cards. I need a way to only count the entire code. I tried to incorporate a space and a comma which are used to separate the codes, but if there is only one code then there is no comma or space. So if a cell only has "P2a", the on my counts tab for "P" and "P2", it will count P2a as well.

Any other suggestions is appreciated.

Jim