COUNT duplicate text strings within a single cell AND a whole column, returning the SUM of all

[mynerva]

Hello
I have come across a couple solutions in this forum which seem to each address two halves of my desire, but I cannot get them to work together!

I have a series of tasks. Each day, 1 or more tasks is completed, and often a task is completed multiple times on the same date. Column B the result of a data validation drop down list, which allows for multiple and duplicate selection, entering the selections into the same cell, separated by commas.

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]July 10[/TD]
[TD]Task A, Task A, Task B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]July 11[/TD]
[TD]Task, B, Task B, Task C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]July 12[/TD]
[TD] Task A, Task B, Task C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]July 13[/TD]
[TD] Task C, Task C, Task C[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to set something up that will now count how many times any given task was completed, both in a single date AND for the whole year.

The resulting count for the above should be:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Task A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Task B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Task C[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

I have tried using LEN and COUNTIF formulas (that I found in the forums) for B1:B3 directly above, but in either cases - using Row 1 as an example -

COUNTIF will count the total Task A's in the range of B1:B4, but it excludes duplicates in the same cell/row - so the total count comes out to 2 (instead of 3). Also, I found that I had to use *wildcards around the text string, or else it would not find the text string!
=COUNTIF($B$1:$B$4,"=*Task A*")


LEN will count the total of Task A's in the cell B1, but if I change the cell to be searched from B1 to a range (B1:B4), it reverts to only counting 1 instance of Task A per cell
=(LEN(B1)-LEN(SUBSTITUTE(B1, A1, "")))/LEN(A1)

I'm not sure what I'm doing wrong, or if maybe there is a better way to accomplish this ....
Hopefully some can help! I'm using Excel 2013 right now, in case that makes a difference.
Thanks!
Mynerva

 

[Jonmo1]

Here's how I see doing it.

Unknown
	A	B	C	D	E
1			Task A	Task B	Task C
2	7/10/2016	Task A, Task A, Task B	2	1	0
3	7/11/2016	Task B, Task B, Task C	0	2	1
4	7/12/2016	Task A, Task B, Task C	1	1	1
5	7/13/2016	Task C, Task C, Task C	0	0	3
6
7			3	4	5
Sheet2

Cell Formulas
Range	Formula
C2	=(LEN($B2)-(LEN(SUBSTITUTE($B2,C$1,""))))/LEN(C$1)
C7	=SUM(C2:C5)
D2	=(LEN($B2)-(LEN(SUBSTITUTE($B2,D$1,""))))/LEN(D$1)
D7	=SUM(D2:D5)
E2	=(LEN($B2)-(LEN(SUBSTITUTE($B2,E$1,""))))/LEN(E$1)
E7	=SUM(E2:E5)

 

[mynerva]

Thanks Jonmo1! It is a bit unwieldy once I put in the 21 actual tasks and task names (which are many more characters long), and a year's worth of dates - but it gets the job done!
Thank you so much!

 

[Jonmo1]

You're welcome.