CODEWITHSUNDEEP
excelcountoffice365
iandavidson1982
iandavidson1982

Reputation: 190

Excel count the number of times a name appears in a range next to another value

The title is confusing but the main reason i'm struggle for an answer is because explaining it in a sentence is tricky!

Basically I have a list of dates, teams and which team won in excel like this: (not sure how to create a table on here)

TEAMS:

Date | winning team | Team 1 | Team 2

11/12/2014 | 1 | a,b,c,d,e | f,g,h,i,j

11/12/2014 | 2 | a,c,e,f,j | b,d,k,i,m

11/12/2014 | 1 | z,x,a,q,r | b,c,d,e,f

I have another sheet which has a list of each player and I want to count the number of times that player has appeared on the winning side:

PLAYERS

Player | on winning side

a | 2

b | 1

c | 1

d | 2

e | 1 etc

There might be an easier way of going about this so i'd be happy to amend the layouts etc. I am basically looking to count the number of teams each player appears on the winning side. Either by matching the player in the correct grouping, matched by the "winning side" value, or by looking up the winning side value then counting up every time a player appears in each range...if that makes sense.

I might be overcomplicating what is a very simple thing to do, there is likely a very easy way of doing this but it's eluding me right now.

Upvotes: 0

Views: 1519

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

This could be done with an array formula. Assuming you have the Excel sheet like this: enter image description here

Then the formula from G2 downwards could be:

{=COUNT(SEARCH($F2,IF($B$2:$B$100=1,$C$2:$C$100,IF($B$2:$B$100=2,$D$2:$D$100,""))))}

To enter a array formula enter the formula in the cell without the curly brackets and then press [Ctrl]+[Shift]+[Enter]. The curly brackets then should appear automatically.

Other approach with players in separate columns: enter image description here

=SUMPRODUCT(($B$2:$B$10=1)*($N2=$C$2:$G$10))+SUMPRODUCT(($B$2:$B$10=2)*($N2=$H$2:$L$10))

Upvotes: 1

Related Questions