Excel help; count multiple characters in a cell

Teh_Next · 6 Feb 2019 at 11:57

I have a column fur reach day of the month and rows of different members of staff.
For each day I place characters and numbers for every member, so as to track stats on time spent on tasks.
The convention is like this...
1a being one person 10 minutes. 2b two persons 15 minutes so on in increments of 5 (a is always 10, the minimum for task)
I can't get a formula to count up all the 1a's for example, or 2b's.
I can do it with just one entry per cell, that's easy but having multiple inputs in one cell is proving harder eg, 1a 2b 2c.
I need to have each entry counted for the whole spreadsheet, including multiple entries in a cell.
Anyone able to help?

james.miller · 6 Feb 2019 at 13:47

Use a CountIf with wildcards for each search term. ie,

Code:

=CountIf([range],"*2b*"

Teh_Next · 6 Feb 2019 at 14:30

Thanks.
I'll give it a go though I have used wildcards before but only one instance of a character per cell was counted.

james.miller · 6 Feb 2019 at 14:40

Oh you need multiple instances in a cell as as well? This will work then:

Code:

=SUMPRODUCT((LEN([range])-LEN(SUBSTITUTE([range],"1b","")))/LEN("1b"))

in english, this compares the length of all the text in the [range] you are searching to the length of that text with search term ("1b" in this case) removed. Divide that by the length of the search term gives you the number of instances of that term in the [range]

Teh_Next · 6 Feb 2019 at 16:54

Thanks, I'll give that a go when I get to the slog tomorrow.