Number of Comma Separated Values

mdsurf

New Member
Joined
Aug 1, 2017
Messages
22
Office Version
  1. 2016
Platform
  1. MacOS
Hi! I play games with my friends and for fun we're tracking how many wins each person has, there are four of us in total but sometimes only three of us play. What I'm trying to do two things, count the total number of games where the 4 of us are present and see each persons win total. We have an excel formatted as follows

Date of match5/1/20205/2/20205/3/20205/4/2020
# of player4343
MarkLiesaLiesaLiesa, YarokKrark
DaltonNekusar, Nekusar, GismoldElendaGrismold
ToryGishathNekusarMarchesa
MattMarisi

1) Question 1: Determining the total number of 4 player games. In the 5/1/2020 column we played a total number of 5 games. Mark won 1 game, Dalton won 3 games and Tory won 1 game. If someone wins multiple games each win is denoted by a comma in the corresponding cell as well as what cards they won with.

What I need to do is check how many players played that day and then sum total number of games played on days with 4 players. In the example above it would be 9. Five games on 5/1 and four games on 5/3.

2) Question 2: Determining the total number of games won by each person where 4 players are present. Very similar to above but just filtering for the player as well.
answer for Mark would be 5, Dalton 4, Tory 3 and Matt 1.

Any help is much appreciated.

Thank you!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,856
How about:

Book1
ABCDE
1Date of match5/1/20205/2/20205/3/20205/4/2020
2# of player4343
3MarkLiesaLiesaLiesa, YarokKrark
4DaltonNekusar, Nekusar, GismoldElendaGrismold
5ToryGishathNekusarMarchesa
6MattMarisi
7
8
9# of 4-person wins
10Mark3
11Dalton3
12Tory2
13Matt1
14
15Total9
Sheet4
Cell Formulas
RangeFormula
B10:B13B10=SUMPRODUCT((LEN($B$3:$H$6)-LEN(SUBSTITUTE($B$3:$H$6,",",""))+1)*($B$3:$H$6<>"")*($B$2:$H$2=4)*($A$3:$A$6=A10))
B15B15=SUM(B10:B13)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,396
Messages
5,769,837
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top