Formula to return number based on cell 'Begins With'

[LMills77]

Hi people,

I need to monitor the how many cells begin with a certain combination.

For some reason, the COUNTIF formula doesn't work for what I need it to.

I have cells that begin with 10/02 or 11/03 - and then comments. However, the formula doesn't pick this up.

Can somebody help? I've attached a workbook to show.

Thank you,

Lee.

[Pepe Le Mokko]

Like this ? =COUNTIF($B$4:$B$21;"10/02"&"*")

[LMills77]

Hi Pepe,

That's great - thank you.

I had to change ; to , - works perfectly!

Lee.

[Pepe Le Mokko]

Oops, I forgot again ( good thing that you spotted it)

[LMills77]

Hi Pepe,

I was wondering - the formula only counts if the cell beings with 10/02 - is there a formula to count the entire number of 10/02s in the cell?

Thank you,

Lee.

[Pepe Le Mokko]

I don't see what you mean

[AliGW]

the formula only counts if the cell begins with 10/02

Not surprising, really, since that's what you asked for.

Like Pepe, I don't understand what you mean by " to count the entire number of 10/02s" - do you need to share an updated sample set of data with us to show what you mean, because it certainly isn't clear from the attachment we have.

[LMills77]

Hopefully I can explain below:

I make a phone call on and put - 10/02.

The COUNTIF can count the beginning and counts 10/02 as 1.

If I phone the next day, I begin the cell with 11/02. In the cell it would look like this:

11/02 - comments
10/02 - comments

The COUNTIF will only count the 11/02 and ignore the 10/02.

I was hoping for a formula to count 10/02 that is buried in the overall cell? I have cells that look like this:

13/02 - misc
12/02 - comments
11/02 - other
10/02 - comments

Does that make sense?

Thanks,

Lee.

[AliGW]

Please provide an updated sample data set showing the new criteria and what you want.

It is NOT good practice to have multiple data items in one cell - consider whether you really need to do it this way. I would suggest that you don't: a normalised dataset will be much easier to maintain and analyse. Put function before form.