Forum menu
Is there a way to do this?
We have a team planning sheet and in row 4, annual leave and our of offices are shown.
Everyone adds their own.
Is there a way to count the occurrences of "OOO" and "A/L" to give an indication of team capacity?
Changing the sheet set up is not an option.
Using a proper tool is not an option.
=SUM(COUNTIF(A1:A4,{“A/L”,”OOO”}))
Change the range to whatever obviously
The SEARCH function will probably be the one here.
You can use it in such as way that it will search for the second occurrence starting at the position of the previous one, plus one.
Then some kind of logic on top to display a 1 for the first find, a 1 for any subsequent finds then total it up.
You can build in different cells and bring together, but SEARCH is a bit tricky to clean up into one cell when you use it as above.
I use SEARCH a lot as people try (nicely) to pack loads of data fields into one 'cell' on accounting software.
Take the below text string in cell A1.
Dave/would/like/to/meet/today
It is clear that / shows a boundary but it is internal to the cell.
In cell C1 you type =SEARCH("/",$A1,B1+1)
This will bring back the position (5) of the first occurrence of /.
If you copy that across to D1, E1 etc it will bring back the position of each subsequent occurrence.
You can then build off of this info to get what you need. I use it often instead of text to columns as text to columns actually changes the source data - something I was taught not to do if you want your methodology to be auditable.
Good luck, there's a way around most things in Excel.
What happens when they're off sick?
Something like
= (LEN(A4) - LEN(SUBSTITUTE(A4, "OOO", ""))) /3
Should give the number of "OOO" s in A4.
Something like
= (LEN(A4) - LEN(SUBSTITUTE(A4, "OOO", ""))) /3
Should give the number of "OOO" s in A4.
Ooh, I like that!
Presumably if one of the codes you are looking for is a text string of 4 characters, you adjust the dividing by three to dividing by four?
Like it, though. Neat.
Yes you'd nest to divide by the number of characters in the search string. Probably also worth changing to
LEN(SUBSTITUTE (UPPER(A4),...
to include instances of OoO or ooo