Formula to count multiple instances of a string in a cell in a range

[KlayontKress]

To all,


I am looking for a way to count multiple instances of a particular string in a cell over a range. I have found a way to count if a single string of characters is in a cell in a range but I'm having trouble counting multiple instances of the same string in a single cell in a range. I found a formula to count multiple instances in a single cell only, but this would require a formula next to each entry in the column to parse this data and then summing those values up. this isn't necessarily a deal breaker if I had just a few strings to look up but I will have several dozen strings to parse and having tens or hundreds of thousands of formulas trying to calculate this would be taxing on excel. I will have thousands of lines of data in the end sheet that is tied to a refresh-able report tied to a data cube.

What I'm trying to do is query results from an audit in which the auditor uses specific codes: 1A, 2A, 3A, Etc. to denote that a particular issue has been found. The data is coming from proprietary software that has "tasks" that get created for an audit, are assigned to a particular individual, and allow for the auditor to put information in an instructions field. I want to query these particular fields to parse that information out so that we can work on training the individuals based on their weak areas. It's possible for a task to have text in the following format: 1A2A1A3A5G6E1A2D3F. If I reorder that and space it apart to make it easier to read, it's: 1A 1A 1A 2A 2D 3A 3F 5G 6E.

What I currently have created is a table with formulas to parse out who the task is assigned to and the 1A, 2A, 3A, etc. codes. This works, but it's only finding single instances in a particular cell but it works over an entire range (I can search the entire columns the data is in). The formula will find the first instance of each string (1A, 2A, 3A, etc.) but not multiple instances of a single string such as 1A 1A 1A.The formula I use to do this is:

=COUNTIFS($Y:$Y,"*"&$A3&"*",$W:$W,B$2)

Where:
A3 is the code we want to query
B2 is the individual we want to query
Column Y has the Codes to be queried from the pivot table
Column W has the Names to be queried from the pivot table

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

Issue Code

Jenny

Bob

Lauren

pivot table starts here

Assigned To:

Instructions

2

1A

2 (Should be 3)

1

Jenny

1A 1A 1B 2A 2B

3

1B

1

Lauren

1A 2A

4

1C

Jenny

5

2A

1

1

2

Bob

2A 2B 2C

6

2B

1

1

Bob

7

2C

1

Lauren

2A

<tbody>
</tbody>

I can use =((LEN(Y2)-LEN(SUBSTITUTE(Y2,$A$2,"")))/LEN($A$2)) to count the multiples in Y2 but I have to have this next to each cell in the Y column and then sum it up. I then need this formula for each code to look up requiring multiple columns of thousands of formulas each to get this information. This requires a lot of time to calculate every time a change is made in excel. Is there a way to adapt this code to look at multiple instances in a particular cell over an entire range?

Any help would be greatly appreciated.


Thanks in advance,

 

[steve the fish]

You can probably do that but before i have a look why do you have (should be 3)? Looks like 2 is correct to me.

 

[steve the fish]

Using your example place in B2:

=SUMPRODUCT(--($W$2:$W$7=B$1),(LEN($Y$2:$Y$7)-LEN(SUBSTITUTE($Y$2:$Y$7,$A2,"")))/LEN($A2))

Drag down and across.

 

[KlayontKress]

Steve,


Thank you for the quick reply. when I initially started writing this reply I couldn't get your formula to work. Then I realized that there was a case difference. I inserted the necessary uppers to account for this and it works beautifully. Thank you for the solution. I didn't realize it would be that easy.