Find exact match in cell with multiple strings

Hey all,

I tried a search with no luck. I have a list of data that looks something like this:

A1=ABC_DEF_1
A2=ABC_DEF_2
A3=ABC_DEF_12
A4=ABC_DEF_1
ABC_DEF_2
ABC_DEF_12
.......

I need to find a formula that will count how many times in this colum ABC_DEF_1 occurs then how many times ABC_DEF_2 occurs.....etc

None of the formulas I have attempted seem to work completely. The formulas seem to pick up only some of the cells, but not all.

Thanks

Try

=COUNTIF(A:A,"ABC_DEF_1")

etc.

that will not return the cell with multiple entries (A4)

Hi

If there are no duplicates in the cell and the values are delimited by a newline, like in the example, try:

=SUMPRODUCT(--ISNUMBER(FIND(CHAR(10)&B1&CHAR(10),CHAR(10)&A1:A4&CHAR(10))))

Where the value you search is in B1. The search is case sensitive. If you want case insensitive, replace Find() with Search().

If there can be duplicates in a cell post back.

HTH

Originally Posted by lecxe

Hi

If there are no duplicates in the cell and the values are delimited by a newline, like in the example, try:

=SUMPRODUCT(--ISNUMBER(FIND(CHAR(10)&B1&CHAR(10),CHAR(10)&A1:A4&CHAR(10))))

Where the value you search is in B1. The search is case sensitive. If you want case insensitive, replace Find() with Search().

If there can be duplicates in a cell post back.

HTH

This looks like money!! Thanks and there are no duplicates within a single cell so this worked perfectly

What results do you expect in the data you show?