Count Total Number of times Two Criteria Match in Different Columns with Partial Text String

[DVDYMBGC]

Hi All,
I've been trying to wrap my head around this for a few hours now. I'm giving in. Please help!


Essentially, I have 3 columns of data, which are filled via Google Form.


I've filled it with the following dummy data:
One column is the time stamp. (A)
One column is one or more names. (B)
One column is one or more basketball shot types. (C)


The form works via multiple choice for 2 of 3 columns, and puts a comma and space between answers when multiple have been selected, so some cells contain a single word and others multiple words with commas and spaces.

Ex:

Timestamp	Player	Shot Type
1/23/2014	Jake T, Rob, Jacob	Hook, Free Throw, Lay Up
2/6/2014	Jake T, Rob, Jacob	Hook
2/17/2014	Jake T, Jacob	Dunk, Hook, Lay Up
2/17/2014	Rob, James	Dunk

<colgroup><col style="width: 190px"><col width="120"><col width="120"></colgroup><tbody style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">
</tbody>

I'm not actually using this for sports, but this scenario might be good for those doing fantasy league stuff.


Essentially, what I want is this:
Part 1
2 Columns Only (B&C):
A formula where I can count the times Name and Shot appear together (in example, name is "Jake T" and shot is "Hook".
This has been problematic for me because I can't get it to count with/without spaces in the data.


I have tried:
=COUNT(FILTER(B:B ; B:B="*Jake T*"; C:C="*Hook*"))

=COUNT(FILTER(B:B ; B:B="Jake T"; C:C="Hook"))

=SUM((B:B="*Jake T*")*(C:C="*Hook*")))

=SUM((B:B="Jake T")*(C:C="Hook")))

I have also tried =Arrayformula(Sum(If(B:<wbr>B="*Jake T*",IF(C:C="*Hook*",1))))

It seems that the problem is that sometimes it's a list of names, and sometimes it is a single name.
For whatever reason, this works to just grab how many times a name appears regardless of whether or not it is a list: =CountIf(B2:B1100,"*Jake T*")



Part 2:
3 Columns:
Same as above, except be able to specify to only pull data from last week, 2 weeks, month, etc,
I have one that counts total baskets that have been made in 4 weeks by all players (ignore NETWORKDAYS, this is just so I can get a working weekday average later):
Pulling data from-

Timestamp	Name	Baskets made
12/2/2013	Michael	1
12/2/2013	Diego	1
12/2/2013	Richard	1

<colgroup><col style="width: 120px"><col width="120"><col width="120"></colgroup><tbody style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">
</tbody>

and formula is "=sumif(A2:A12369;">="&(Today(<wbr>)-NETWORKDAYS(TODAY(),TODAY()+<wbr>28,));C2:C12369)"



A new table will populate shots by player using formulas, pulling from the form data, like:

Hook Shots In The Last:	Jake T	Rob	James
Week	Formula	Formula	Formula
2 Weeks	Formula	Formula	Formula
3 Weeks	Formula	Formula	Formula
Month	Formula	Formula	Formula

<colgroup><col style="width: 120px"><col width="120"><col width="120"><col width="120"></colgroup><tbody style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">
</tbody>

Other tables will be made to calculate other shots in a similar way.


This is so I can keep an eye on how many times Jake T (and everyone else) has worked on Hook shots (and others) in the last 1 week, 2 weeks, 1 month, 2 months, etc.

Sample Sheet
https://docs.google.com/<wbr>spreadsheets/d/<wbr>109e03TN9zQpIc5FUf42rGSwUPHoZu<wbr>tnCtYm-AXSEO_c/edit#gid=0


Hopefully I've been clear enough. Thanks for you're time!
Cross posted after no response in over 3 days: https://productforums.google.com/fo...documents/how-do-i/chrome-browser/c9LWzwLYCgU

 

[barry houdini]

Hello and welcome to MrExcel

Looks like you have "new google sheets" in which case you can use COUNTIFS function (with an "S" on the end), e.g.

=countifs(B:B,"*Jake T*",C:C,"*Hook*")

You can easily add extra criteria to that so for same criteria but within last two weeks

=countifs(B:B,"*Jake T*",C:C,"*Hook*",A:A,">="&today()-14)