COUNTIF Statements

**macka1987** · 06-04-2011, 06:47 PM

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.

**JBeaucaire** · 06-04-2011, 08:09 PM

Assuming the string ‘Linda+1; George+2; Albert+6’ is the full string all found in cell C5 (not split between other cells), then assuming your 6 examples above would thus be in cells C5:C10, this SUMPRODUCT() construct will do what you've asked initially:

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1&"+"&B1,$C$5:$C$10))), --(ISNUMBER(SEARCH(A2&"+"&B2,$C$5:$C$10))), --(ISNUMBER(SEARCH(A3&"+"&B3,$C$5:$C$10))))

You could change the range to C5:DD32 and it should still work.

**macka1987** · 06-05-2011, 02:57 AM

Thank you JBeaucaire for your help, that function works well on what I initially asked, however unfortunately I cannot get it to work on my last point!

Keeping all of the functionality of that function, we still have the following:

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.

I would also like the function 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’.

Cheers

**JBeaucaire** · 06-05-2011, 04:40 PM

It's ugly, but it works:

=SUMPRODUCT(--(ISNUMBER(SEARCH(IF(B1=0, "*", A1&"+"&B1), $C$5:$C$10))), --(ISNUMBER(SEARCH(IF(B2=0, "*", A2&"+"&B2), $C$5:$C$10))), --(ISNUMBER(SEARCH(IF(B3=0, "*", A3&"+"&B3), $C$5:$C$10))))