Need help with countif/countifs

[Tony0731]

Hi guys,
Can I have some expert help with an issue I am trying to solve.?
Problem is I want to count multiple entries in one cell as per the attached. Can someone please advise if this is at all possible and if so what formula should I use?

Thanking you all for your patience and support,
Tony

[Glenn Kennedy]

One way:

=SUMPRODUCT(--ISNUMBER(SEARCH(ROW(A1),$C$4:$C$8)))

copied down

[Tony0731]

Crikey Glenn,
You came up with the solution so quickly, and it works fine, only problem is I have not got a clue what the formula means or does, can you explain please?
Sorry to be a pain, but I would love to understand what has been applied here.

Thanks in advance,
Tony

[pjwhitfield]

One way:

=SUMPRODUCT(--ISNUMBER(SEARCH(ROW(A1),$C$4:$C$8)))

copied down

If the number of sports go above 9 then it will count 10, 11 etc as part of the Football results.

I would also say though that the solution provided is "too clever" as such by referring to a row number to search and is working on an assumption that the full table would run in order etc (edit: however I have just seen your proposal to split column E which solves that).

[Glenn Kennedy]

Though, I'd be tempted to split column E into 2, as shown here.

[Tony0731]

This just gets better !!

[Glenn Kennedy]

The Cyan isn't easy to see beside the green, but it's the -- in the formula.

[Tony0731]

Thanks so much Glenn, This is exactly what I wanted and through your help can now see what the formula means.

Thanks once again,
Tony

[Glenn Kennedy]

Explanation (of second formula).

=SUMPRODUCT(--ISNUMBER(SEARCH(F4,$C$4:$C$8)))

Red: search for this number
Orange; In this range
Green: returns TRUE if the number is found, otherwise FALSE.
Cyan: convert TRUE to 1, FALSE to 0
Black: add up the 1s and 0s.



You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Glenn Kennedy]

You're welcome.


BtW, the best way to see what a formua is doing is via:

Formulas/Formula Auditing/Evaluate Formula.

You can then step through the formula to see what it does. Rather annlyingly, the evluation box cannot be resized.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Tony0731]

Thanks Glenn, That`s something else I have learnt today - what a great site this is.
Will mark it as solved,
Cheers buddy.
Tony