Question for Excel gurus here

LionAquaLooper

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.  

SteveRobinson

If it's always the first three characters can you use the left() function? Otherwise use instr()

stickyfiddle

Use an extra column. Either to do the check for the 123 then sum them, or to extract the first N characters then do a countif 

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... 

LionAquaLooper

I've toyed with the idea of incorporating the length as a search parameter but unfortunately I've got thousands of these things to search and have differing lengths, and a lot have identical first n characters.  Basically too many variables.

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.  

drofluf

Does countif(left(range,3)=123) work. On holiday so no access to excel to check

scrumhalf

You can search for text within a string, can't remember the formula offhand. Have another column, 1 if it's in the string and 0 if it isn't.

There are probably more efficient ways of doing it but that works.

Sporky

Can I just check - you don't want 1234.blah to be counted, nor 9123.blah?

So the bit before the delimiter must be exactly "123"?

LionAquaLooper

drofluf said:

Does countif(left(range,3)=123) work. On holiday so no access to excel to check

No, I wish it was that simple.  As I mentioned above, in the thousands of strings I'll be searching for the first 3, 4, 5 etc characters might be identical.  What makes them unique might be the last n or middle n characters etc.  So just as examples:

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".    

Sporky

Also is abc or xyz and so on letters or numbers? 

LionAquaLooper

Sporky said:

Can I just check - you don't want 1234.blah to be counted, nor 9123.blah?

So the bit before the delimiter must be exactly "123"?

Correct.  If 123 is to be counted, it has to be "123" or "123.blah" only.  
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.  

swiller

if left(a1=123 and mid(a1,4,1="."),"Y","N")
Maybe what you are looking for, unless you have xx123xx and you want to flag that as a count.

LionAquaLooper

Sporky said:

Also is abc or xyz and so on letters or numbers? 

Quickly eyeballing my data they're all letters.

swiller said:

if left(a1=123 and mid(a1,4,1="."),"Y","N")
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

swiller

LionAquaLooper said:

Sporky said:

Also is abc or xyz and so on letters or numbers? 

Quickly eyeballing my data they're all letters.

swiller said:

if left(a1=123 and mid(a1,4,1="."),"Y","N")
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

thats correct, but does work in your original examples, so wouldnt count 1234 as that would return N because char 4 is not a full stop.

Sporky

I'm really not sure what you're after.

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?

LionAquaLooper

Sporky said:

I'm really not sure what you're after.

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?

Hopefully this illustrates it better:



I just put the expected results in column H but that's where I want to put a formula in.

crunchman

You should be able to use a FIND() function to find the position of the fullstop, then put the result of that into another function.  E.g. if the the full stop is at position 10, then the find function will return 10.  Then you can search the string up to the find function result -1 for the 123 string.

The problem is that the FIND() function will return a #VALUE! error if the full stop isn't there, so you will need to handle that otherwise it will probably break the Countif function.  There are functions like ISERR() and ISERROR() but it's going to get very complicated to fit those into a single column with all the other logic.

Personally I'd use VBA.  Written out in VBA it will be far easier to understand the logic than a horrible nested function.  You can create VBA functions that you can then use as a worksheet function if you want to do it that way.  If you don't want to do that, then split it into multiple columns.  Hide the columns that are used for the calculations to make it look nicer.

JCA2550

Is the data source in the same column or any where on the spreadsheet?

stickyfiddle

LionAquaLooper said:

Sporky said:

I'm really not sure what you're after.

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?

Hopefully this illustrates it better:



I just put the expected results in column H but that's where I want to put a formula in.

What is your logic that would mean 123.xyz would be counted but 1234 or 1234.xyz wouldn't be? 

What about 123xyz? 

Sporky

That's why I asked that set of questions - to find out what problem we're trying to solve. 

stickyfiddle

Sporky said:

That's why I asked that set of questions - to find out what problem we're trying to solve. 

Yep completely agree. 

But I'm also well convinced the best solution is a helper column. 

Sporky

I suspect you're right. Helper columns are good things.

I reckon this could be assembled into a lambda too.