Counting number of occurences of a specific search string

user_18102739 0
Viewed 48

I'm building a monitoring system that takes a log (where people register their work in a set format) and returns a counter, which I can use for analysis. The monitor and log are two separate workbooks. The log has entries like this: INITALS;DATE;HOUR:RESULT|

Each cell can contain multiple entries.

My first attempt was to do a simple countif and look for a string (note that I use ; instead of , in formulas since I work on a Dutch excel):

=COUNTIF('LOCATION'!Table[LOG];"*NB;??/??/????;??:??:#A*|*")

This worked fine, but the formula only counted the number of cells where this string was present, not the actual number of occurences. I then tried this solution.

=SUM(LEN('LOCATION'!Tabel13[LOG])-LEN(SUBSTITUTE('LOCATION'!Tabel13[LOG];"NB";"")))

This indeed counted the number of times "NB" was present in the LOG. However, when I tried to use the original search string, this solution stopped working:

=SUM(LEN('LOCATION'!Tabel13[LOG])-LEN(SUBSTITUTE('LOCATION'!Tabel13[LOG];"*NB;??/??/????;??:??:#A*|*";"")))

It seems to me that SUM does not recognize symbols like ? or * which are necessary to define the correct search string. Where did I go wrong? Or can this be solved in another way? I can still look into VBA, but the workbooks are slow as hell already.

excel-formula excel
1 Answers

"?" and "*" are wildcards. Some functions support these (like COUNTIFS()) where others don't. Like you found out, SUBSTITUTE() does not.

Here is one way to count, assuming ms365:

enter image description here

Formula in C1:

=REDUCE(0,A1:A2,LAMBDA(a,b,a+LET(X,SEQUENCE(LEN(b)),SUM(--(IFERROR(SEARCH("NB;??/??/????;??:??:#A*|*",b,X),0)=X)))))

Note: I removed the asterisk in front of "NB" just to make searching for a position valid in comparison to what i called variable "X".

user_97581940
Related