Finding and Counting for text within a string in a column

Hi,

I'm trying to teach myself excel and apply what I learn at work. Here's my problem:

AF26AYN25
AF25BYN21
AU34CNYY22
CE221AYYY10
EU13BYN2
GR83DYN1
MI14BNYY15
NO53BYN6
PA66BYN25
SO51CYN5
AS44DNYN8

These serial numbers represent buildings in various places around the world.

I'm trying to count particular types of buildings, such as, all the buildings in Asia (AS in the first two digits).

Creating the serial numbers was my "bright" idea for simplifying finding and counting from a table with lots of conditional logic (CountIf Column A="Asia", and Column B="B", and Column C="Yes", and Column D="No"...). I could not get a countif formula to work with and: AND(COUNTIF( , ), COUNTIF( , ). If comes back true, but doesn't count the cells I asked it to.

So, I build this code system, which may be much better in the long run, or I may have wasted all afternoon now that I realize I don't know how to search within the string. I tried simply using CountIf with all wilcards except for the text in the digits I'm interested it: (AS????5????25), which doesn't work. Here's the code table:


Region Code City Code Asset Type Code Criticality Code Plan Created Code Satellite Code Hub City Hub Plan Code Risk Profile Code Asset Code

AF 2 6 A Y N 25 AF26AYN25
AF 2 5 B Y N 21 AF25BYN21
AU 3 4 C N Y 22 Y 22 AU34CNYY22
CE 22 1 A Y Y 1 Y 10 CE221AYYY10
EU 1 3 B Y N 2 EU13BYN2
GR 8 3 D Y N 1 GR83DYN1
MI 1 4 B N Y 2 Y 15 MI14BNYY15
NO 5 3 B Y N 6 NO53BYN6
PA 6 6 B Y N 25 PA66BYN25
SO 5 1 C Y N 5 SO51CYN5
AS 4 4 D N Y 6 N 8 AS44DNYN8

Am I just making my life harder for myself? Should I just build lots of smaller tables and use the sum function for each specific type of building I'm looking for (dozens of tables). I honestly thought I'd be able to search for the text in the string with one function, and it would be clean and easy.

Any help?

Post a workbook with a sample table and indicate what you counted and why you got the number you got.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

One way:

=COUNTIF(A:A,"AF*")


Regards, TMS

Hi and welcome to the forum :0

Im a little unsure on what your data looks like (columns/rows etc). Could you upload a sample worlbook, showing what you have (no sensitive info), what your expected outcome would be, and how you arrived at that?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

For Starters (though I'm not sure about all those spaces):

Please Login or Register  to view this content.

Originally Posted by WanderingDog

Hi,
Creating the serial numbers was my "bright" idea for simplifying finding and counting from a table with lots of conditional logic (CountIf Column A="Asia", and Column B="B", and Column C="Yes", and Column D="No"...). I could not get a countif formula to work with and: AND(COUNTIF( , ), COUNTIF( , ). If comes back true, but doesn't count the cells I asked it to.

Is it a "bright" idea to do the combination while COUNTIFS or SUMPRODUCT can do this?
With table like this:
A B C D E F G
AF 2 6 A Y N 25
AF 2 5 B Y N 21
AU 3 4 C N Y Y 22

Count A column with "AF" and C column equals 6:
=COUNTIFS(A2:A4,"AF",C2:C4,6)
=SUMPRODUCT((A2:A4="AF")*(C2:C4=6))
=1
Does it work?

Guys, if you look at te provided workbook, there are sample answers in there that, so far, would not work with any of the suggestions

Originally Posted by FDibbins

Guys, if you look at te provided workbook, there are sample answers in there that, so far, would not work with any of the suggestions

The OP said in #1 that he couldnot use COUNTIF for multi columns from original table, so he have to create combination code to search

Please Login or Register  to view this content.