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:
<tbody>
</tbody>
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:
<tbody>
</tbody>
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
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:
A | B | |
1 | July 10 | Task A, Task A, Task B |
2 | July 11 | Task, B, Task B, Task C |
3 | July 12 | Task A, Task B, Task C |
4 | July 13 | Task C, Task C, Task C |
<tbody>
</tbody>
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:
A | B | |
1 | Task A | 3 |
2 | Task B | 4 |
3 | Task C | 5 |
<tbody>
</tbody>
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