I need a variation on COUNTIF because in this scenario it isn't so simple.
I want to count how many times "123" appears on this list:
123
1234
123.xyz
123.abc
1234.def
I only want the search to focus on the part of the string before the full stop. The result should be 3 but obviously doing a stock COUNTIF will only return 1.
I can simply split the cells by the full stop delimiter and end up with 2 columns but I'm wondering if there's a way to do this using a formula so I don't have to split the list into 2 columns?
Any suggestions would be most appreciated! Cheers.
Comments
I'm sure there's a way to do it as a single formula but future you won't thank you when you have to change it later and have forgotten how it works...
InStr () is a VBA function isn't it? Don't really want to go VBA route yet as I'm looking for a formula solution I can apply to different workbooks in the future.
There are probably more efficient ways of doing it but that works.
So the bit before the delimiter must be exactly "123"?
ABC123DEF
ABC456DEF
ABC123HIJ
ABC321HIJ
And there will be some where the character length is shorter/longer. So it's really the whole string or bust.
The formula i'm looking for should do "if the string is found before the left-most full stop in the range, count it".
The left-most full stop is key I think but I can't think of a way to make the search to focus only on the part before that full stop.
p.s. I also need the search to work on a whole column (like COUNTIF does), and not just search within a single cell. So FIND and SEARCH are out of the question I think. But I'm currently playing with those functions to see if I can somehow embed them in a COUNTIF formula.
Maybe what you are looking for, unless you have xx123xx and you want to flag that as a count.
EDIT: just re-read this and won't work because unless I'm mistaken this formula assumes the 4th character is the full stop. Unfortunately not always the case
You have lots of strings, some of which have a full stop delimiter in them?
You want to count how many of them begin with another string?
Is the string you want to count actually "123", or do you want to enter a string of arbitrary length in a cell and get an answer?
If they do not have a delimiter, you want to count them if they exactly match the other string?
If they do have a delimiter, they should only be counted if the delimiter is immediately after the other string, and the other string is the only thing before the delimiter?
I just put the expected results in column H but that's where I want to put a formula in.
http://www.theboxwoodchessmen.com/
https://www.facebook.com/tingiants/?view_public_for=231700547508938
What about 123xyz?
But I'm also well convinced the best solution is a helper column.
I reckon this could be assembled into a lambda too.