Count Max value (text+number) occurrences with filtering a specific part of text

[gsvirdi]

I'm having an excel column range of Text+Numbers (including blank cells) something like:

00EGB00-GE001
00EGB00-GE001
00EGB00-GE001
00EGB00-GE001
00EGB00-GE002
00EGB00-GE002
00EGB00-GE002
00EGB00-GE002
00EGD20-GD101

First what I need is to Count total number of similar values and I'm stuck with the logic for counting total unique "similar" values... example "GE" & "GD" separately.

How to count total number of unique "GE" values in the list? I thought =COUNTIF(B:B,"*GE*")should work but it does not. It gives total count of "GE" (8 total) but I need to find unique count.

Example GE001 & GE002 should be considered as 2 values and shown as 2 in total.

Kindly help

 

[azumi]

Maybe (Assumed your data in ranges A1:A9)
=COUNT(SEARCH("GE",A1:A9))

Array Formula (You need to confirm press CTRL-SHIFT-ENTER Button together)

Change with other criteria

 

[Somendra Misra]

@gsvirdi

Correct me if I had not understood your problem.
Assuming data in I3:I11, below formula will give the count of unique items containing "GE".

=SUMPRODUCT(--(FREQUENCY(IFERROR(MATCH(IF(ISNUMBER(SEARCH("*GE*",I3:I11)),I3:I11,"NA"),IF(ISNUMBER(SEARCH("*GE*",I3:I11)),I3:I11),0),"-"),ROW($I$3:$I$11)-ROW($I$3)+1)>0))

Regards,

 

[AIM]

@gsvirdi

You should be using =COUNTIF(B:B,"*GE001*") and =COUNTIF(B:B,"*GE002*") to get the unique count for GE001 and GE002.

Using "*GE*" as your search criteria woul give you 8 as result, as the countif formula is searching for GE and not GE001, GE002, GExxx, etc.

Regards!!

 

[gsvirdi]

hahaha wish if that was so easy my friend, but u have already reached the first step towards the solution .
Okie so now as we have counted all cells having GE in it, (total 8 times). GE001 appears 4 times and GE002 appears another 4 times, but now we have to count the number of unique texts out of all these 8 counts we got. U should again have a look at the sample array I posted in the question.
GE001 should be one value and GE002 shall be second value and GE003 shall be third value and so on... So effectively as per the sample array the unique count of GE shall be 2 only. (that is GE001 & GE002)
Any ideas now?

 

[Somendra Misra]

@gsvirdi

Have you tried my formula?

Regards,

 

[gsvirdi]

@gsvirdi

Correct me if I had not understood your problem.
Assuming data in I3:I11, below formula will give the count of unique items containing "GE".

=SUMPRODUCT(--(FREQUENCY(IFERROR(MATCH(IF(ISNUMBER(SEARCH("*GE*",I3:I11)),I3:I11,"NA"),IF(ISNUMBER(SEARCH("*GE*",I3:I11)),I3:I11),0),"-"),ROW($I$3:$I$11)-ROW($I$3)+1)>0))

Regards,

This is a automatically created database file in the network and I'm not suppose to edit it, it can contain non-blank cells anywhere in the column. It can contain any number of data values (GE was just an example) so I've to keep the searching approach as open as possible. First search all values of GE then search all unique values after GE (that is GE001, GE002, GE007, etc)

 

[Somendra Misra]

My formula is giving result 2 which is the unique count of list with GE in it for your sample data, if you change any one GE002 to GE003 you will get 3.

Regards,

 

[gsvirdi]

With fixed number of ranges as u have used it will work. But as my requirement is to take care of an unmonitored column which somebody else is updating automatically from their database... I'm not aware of blank cells, even the starting cell in the column can be blank.... or can have many duplicate entries, or additons & deletions anywhere in that column.... and that is creating a mess for me.

 

[Somendra Misra]

@gsvirdi

This formula can take care of blanks. Regarding range there are two options with you.
1. Manually change the range each time you want to do the calculation.
2. Create a dynamic named range to be used in formula, so each time you will update the data you will get automatic results.

Regards,

 

[Haseeb A]

Hello,

If you want to count if Range 'contains' a value. eg: GE, then you could use

=SUMPRODUCT(ISNUMBER(SEARCH(SearchValue,Range))/COUNTIF(Range,Range&""))

...First search all values of GE then search all unique values after GE (that is GE001, GE002, GE007, etc)

If you want to count only after GE (after the search value)

=SUMPRODUCT(ISNUMBER(SEARCH(SearchValue,Range))/COUNTIF(Range,"*"&MID(Range,SEARCH(SearchValue,Range&SearchValue),250)))

Change Range & SearchValue to your cell reference.

As Misra said, you can use dynamic range in the formulas. So Add/Delete will be adjusted accordingly.