Home>
A B C D E
1 First place 2nd place 3rd place 4th 5th place
2 Tanaka Suzuki Okamoto Oda Ohashi
3 Kuroda Takahashi Tanaka Ohashi Miyamoto
Four Takahashi Okamoto Suzuki Ota Miyamoto
Five Okamoto Oda Kuroda Takahashi Ota
6 Hashimoto Suzuki Okamoto Takahashi Ohashi
7 Tanaka Oda Suzuki Ota Kuroda

If i have a table like the one above
I want to find out the number of records including Mr. Tanaka and Ohashi
The desired return value is 2

Should I put in such an expression?

What I tried
COUNTIFS It is difficult to specify conditions because Mr. Tanaka's position is different.

What I looked up
VLOOKUP
COUNTIF

Such

[Supplement]
The search conditions are not limited to Mr. Tanaka and Mr. Ohashi.
I would like to get the number of each combination.
Number of combinations of Mr. Tanaka and Ohashi
Number of combinations of Mr. Tanaka and Suzuki
Number of combinations of Mr. Tanaka and Okamoto
Number of combinations of Mr. Tanaka and Oda
Or
Number of combinations of Mr. Okamoto and Ohashi
Number of combinations of Mr. Okamoto and Suzuki
Number of combinations of Mr. Okamoto and Oda
Such

  • Answer # 1

    How about having a working column in column F, combining names, and using COUNTIFS?
    It is convenient to create a combination table on the right side so that you can use it as it is even if additional records are added.

    = CONCATENATE (A2, "-", B2, "-", C2, "-", D2, "-", E2)
    = COUNTIFS (F: F, "*"&H2&"*", F: F, "*"&I2&"*")

  • Answer # 2

    excuse me. It may be roundabout.
    Judge whether the search record target (return value: 1) or not (return value: 0) in column F,
    If you sum the total value with SUM in column F, row 8, 2 will be returned.
    I hope there is no difference in recognition.

    'A B C D E F
    1 1st 2nd 3rd 4th 5th
    2 Tanaka Suzuki Okamoto Oda Ohashi = IF (COUNTIF (A2: E2, "Tanaka")>0, IF (COUNTIF (A2: E2, "Ohashi")>0,1,0), 0)
    3 Kuroda Takahashi Tanaka Ohashi Miyamoto
    4 Takahashi Okamoto Suzuki Ota Miyamoto
    5 Okamoto Oda Kuroda Takahashi Ota
    6 Hashimoto Suzuki Okamoto Takahashi Ohashi
    7 Tanaka Oda Suzuki Ota Kuroda
    8 = SUM (F2: F7)

    I will fix it.
    Create a separate combination table you want to investigate (combination table in column A, row 10g)
    By using that value as a condition, I think it is possible to extract records that have any combination.

    'A B C D E F
    1 1st 2nd 3rd 4th 5th
    2 Tanaka Suzuki Okamoto Oda Ohashi = IF (COUNTIF (A2: E2, $B $10)>0, IF (COUNTIF (A2: E2, $C $10)>0,1,0), 0)
    3 Kuroda Takahashi Tanaka Ohashi Miyamoto
    4 Takahashi Okamoto Suzuki Ota Miyamoto
    5 Okamoto Oda Kuroda Takahashi Ota
    6 Hashimoto Suzuki Okamoto Takahashi Ohashi
    7 Tanaka Oda Suzuki Ota Kuroda
    8 = SUM (F2: F7)
    9
    10 Union table Tanaka Ohashi