Count with multiple text in a cell

[HCZ]

Hello

I have an incident dataset with multiple text values (with alt+return as delimiter) for incident category, as shown in column B in the attached file.

What I want is to create a report based on a reporting category (column E) if it is in column B. For example, for ID 2368653, it will be counted in reporting category Collision, Collision/Near Hit, Near Miss with Person, Near Hit. for ID 2809463, it will be counted in reporting category Collision/Near Hit, Collision, Near Hit, Near Miss with Person, and so on. And total count of Collision will be returned in cell F4, total count of Collision/Near Hit will be returned in cell F5, and so on.

I used Search function to create a metrix. But it double count Near Hit because it appears in Collision/Near Hit category as well.

Can anybody help me to solve the issue? I use Microsoft 365.

Many thanks in advance.

[BadlySpelledBuoy]

One way would be to use TEXTSPLIT to separate the values out into separate columns based on the alt+return (CHAR(10)), then use COUNTIF on the results.

Please Login or Register  to view this content.

See attached.

BSB

[HCZ]

This is brilliant. Thank you very much.

where can I get a list of delimiters? eg. CHAR(10) for alt+return, what is for comma or hyphen etc?

[AliGW]

Here: https://www.ascii-code.com/

But hyphens and commas would go into the formula as is: =TEXTSPLIT(B4,",") or =TEXTSPLIT(B4,"-")

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[BadlySpelledBuoy]

Ali's link will give you all the codes, but you can get them quickly within Excel by putting the character in a cell and using the CODE function to find it. i.e. a comma in A1 and the formula =CODE(A1) will return the relevant CHAR value. 44 for a comma, 45 for a hyphen etc.

BSB

[windknife]

Clear all expected results.
Try this in F4.

Please Login or Register  to view this content.

[AliGW]

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for? There is no Excel 11 ...

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[HCZ]

Hi All

Thank you very much for your help. You're all genius!!!

[windknife]

You are welcome.

[wk9128]

CELL F4 FORMULA

Please Login or Register  to view this content.