Evening everyone,

I have a large spreadsheet with text strings in cells C5:DD32. Below are six examples of the format the text strings are in:

Linda+1; George+2; Albert+4
Albert+4; Linda+3
George+2; Susan+6
Albert+2; Linda+2
Linda+1; George+2; Albert+4
Linda+1; Albert+6; George+2;


I would like a formulae where I can search for the number of times the combination ‘Linda+1; George+2; Albert+4’ occurs in these cells. So in this example, the output should be 2.

NB. It should be noted that the order can change per cell.

Ideally, I would like to be able to change the combination the function searches for by simply altering the values in given cells. So if I want to search for the number of times the combination ‘Linda+1; George+2; Albert+6’ occurs, I would have:

In A1, I have ‘Linda’ and in B1 I have the number 1.
In A2, I have ‘George’ and in B2 I have the number 2.
In A3, I have ‘Albert’ and in B3 I have the number 6.

NB. It should be noted that the order can change per cell.


Lastly, if I was able to alter the combination the function searches for by simply altering the values in given cells, as listed above, I would also like it if when a 0 is placed in B1 for example, the function would only search for ‘George+2, Albert+6’ in any order i.e Both ‘George+2, Albert+6’ and ‘Albert+6’, George+2’.

Many thanks for your help in advance.