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?
Bookmarks