Hi,
I am a basic excel user, I am handling an excel work book in 2016 version, I’ve got a query as follows:
I have 9 worksheets in an excel book and a specific column in each sheet which displays a series of reference numbers as:
GAD5-CDC-T2-349-230315-DWG-PP-STR-0114-0
GAD5-MGM-T2-349-230315-DWG-PP-STR-0114-0
GAD5-CDC-T2-349-230315-DWG-SD-STR-0114-0
GAD5-MGM-T2-363-250315-DWG-PP-STR-0119-0
GAD5-CDC-T2-363-250315-DWG-DD-STR-0119-0
GAD5-CDC-T2-363-250315-DWG-BD-STR-0119-0
GAD5-CDC-T2-259-51-050515-DWG-FD-S-0233-00
GAD5-CDC-T2-259-51-050515-DWG-TD-S-0233-00
GAD5-MGM-T2-259-51-050515-DWG-LD-S-0233-00
GAD5-MGM-T3-119-25-DDS-ST-1568-02
GAD5-MGM-T3-119-25-DDS-RT-1568-02
GAD5-MGM-T3-119-25-DDS-OT-1568-02
and so on, Please note: these numbers are not following any sequence. I want to count the number of times the second last series of number is appearing. In this Instance
0114 is 3 times
0119 is 3 times
0233 is 3 times &
1568 is 3 times
I want to count this by a formula and get the result in any other cell as a counter. I’ll appreciate if you could help me in this please. Attached is the sample file.
Hi,
maybe
=COUNTIF(A:A,"*-0114*")
could do the trick.
If you'd need to check the substring (-0114) only in the last 8 digits
=SUMPRODUCT(--ISNUMBER(SEARCH("-0114",RIGHT($A$1:$A$2511,8))))
Hope it helps
Hi syediomer,
There are various tools you can use to extract the numbers you want to count, but the key is that the data must be consistent, and unfortunately yours isn't.
However, the attached file shows the use of the MID function to extract the numbers you want to count and then a PivotTable to count them ('MID Formula sheet).
The Power Query sheets uses Power Query to extract the numbers you want to count and then a PivotTable to count them.
Both require your data to be cleaned so it's consistent. You'll see there are two problem codes in the PivotTable.
I hope one of those approaches is useful.
Mynda
Hi Mynda,
Appreciated many thanks. I've gone through the attachment, and the links to understand MID search option. I am sure this will help me, but I have to redo the formula for set of cells based on the position of the numbers I want to count, as you can see that the data is not consistent at all.
To elaborate a bit more I have 8 Workbooks each of them contains 10 sheets approx. and all the sheets have this type of columns. So I guess I will spend some serious amount of time in formulating the sheets. However I'd like to thank you for your response.
Regards.
canapone said
Hi,maybe
=COUNTIF(A:A,"*-0114*")
could do the trick.
If you'd need to check the substring (-0114) only in the last 8 digits
=SUMPRODUCT(--ISNUMBER(SEARCH("-0114",RIGHT($A$1:$A$2511,8))))
Hope it helps
Hi Canapone,
The fact is I am not looking for a formula to count specific number sequence, if you check the attachment in my question there's lots of inconsistency in the data, even the position of the numbers is inconsistent. That's the way it is. However Thank you so much for your kind response.
Regards.
maybe you could just create an additional column in your table, break off the last part of your number and past it in the new cell, for example
GAD5-CDC-T2-349-230315-DWG-PP-STR in Cell A1 and 0114-0 in Cell A2. If you need to, select both adjoining cells and make it a range.
Might Work for your purpose
