Track input on daily basis

[E. Jones]

Hello all, here is my dilemma:

I have a spreadsheet that lists a number of items that need to be completed by the end of a given week, and the list can range from 100-140 from week to week. I already have working formulas that track the total tasks for the week, how many completed, how many are left to do of each tasks, and the total amount left, the average number needed to complete on a given day to complete the list by the end of the week, and so on.

When a task is completed, I simply put an "X" next to it, and all the calculations are made, including CF that highlight the row as being complete.

What I can't figure out though, is how to track how many tasks I've completed on a given day. For example: I have 100 tasks to do for the week, and on Monday I completed 25 of them, leaving me 75 for the rest of the week. On Tuesday, I completed 10, which now leaves 65 left. What I want is to be able to show a running count for tasks completed on each day, without affecting the tasks that were completed on previous days. Hope that makes sense.

So, Monday would show 25, Tuesday would show 10, and as I complete the tasks on Wednesday, it will sum how many times "X" has been placed in column A on Wednesday only, then the same for Thursday, etc.

I have listed a very abbreviated list below to try to help illustrate what I mean.

As usual, any and all help is much appreciated!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]COMPLETE?[/TD]
[TD="align: center"]TASK[/TD]
[TD="align: center"]DUE BY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Install new cabinets[/TD]
[TD]Tue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]X[/TD]
[TD]Paint the walls[/TD]
[TD]Wed[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Lay the carpet[/TD]
[TD]Wed[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]X[/TD]
[TD]Clean garage[/TD]
[TD]Thu[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]X[/TD]
[TD]Fix plumbing[/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Paint the walls[/TD]
[TD]Fri[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to track how many task's I completed on a given day, the actual task completed is unimportant, as the task will appear in column B multiple times.

 

[sheetspread]

countifs or the pivot table will do this

 

[E. Jones]

Meaning exactly what? I understand that countifs will count if certain criteria are met, but how would the countifs determine that the "X" was input today and not two days ago? Please explain.

 

[sheetspread]

It would be easier if your days were recorded as dates instead of text names (you can format them as such if desired). Then include a less than/between condition in the formula or pivot table field.


If the day names only apply to the current week, you can leave them and just include as another condition.

 

[E. Jones]

In my actual spreadsheet, column C is listed by dates, so for last week, they were 1/15/18, 1/16/18, and so on, with about 20 items being due on 1/15/18, another 26 due on 1/16/18, and so forth. I sometimes complete the tasks before the due date, for example, something isn't due until Thursday, but it is finished on Tuesday. This seemed like a simple add to the spreadsheet, but unfortunately, I haven't been able to figure it out.

I appreciate the help thus far though.

 

[sheetspread]

So in column C are the due dates, is there a column with completion dates?

 

[E. Jones]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Task "A"[/TD]
[TD]Task "B"[/TD]
[TD]Task "C"[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]assigned[/TD]
[TD]34[/TD]
[TD]52[/TD]
[TD]36[/TD]
[TD]122[/TD]
[/TR]
[TR]
[TD]completed[/TD]
[TD]17[/TD]
[TD]11[/TD]
[TD]35[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]left[/TD]
[TD]17[/TD]
[TD]41[/TD]
[TD]1[/TD]
[TD]59[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1/15[/TD]
[TD]1/16[/TD]
[TD]1/17[/TD]
[TD]1/18[/TD]
[TD]1/19[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]assigned for completion by[/TD]
[TD]25[/TD]
[TD]28[/TD]
[TD]12[/TD]
[TD]30[/TD]
[TD]27[/TD]
[TD]122[/TD]
[/TR]
[TR]
[TD]completed[/TD]
[TD]25[/TD]
[TD]28[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]left[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD]30[/TD]
[TD]17[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So what I need, is to track how many I completed on 1/15, how many on 1/16, etc. I already know the total amoumt completed, and the total amount of each task. I need to know if say on Thursday, I can look back and see that I finished "X" number on any previous day, because, even though all task for 1/16 are completed in this example, 5 of them may have been completed on 1/15, for a total completion of 30 on 1/15, and 22 completed on 1/16.

Assuming today is Wednesday 1/17, I have so far not completed anything, but by the end of the day, I will have completed everything left for 1/17, and 10 that are due for 1/18, for a total complete on 1/17 of 22.

Hope that makes sense.

 

[sheetspread]

If you record your data in a normal format you can then insert two pivot tables:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Task	Date	Status	Count
2	B	1/3/2018	Assigned	1
3	A	1/3/2018	Assigned	1		Sum of Count	Column Labels
4	C	1/3/2018	Assigned	1		Row Labels	A	B	C	D	E	F	Grand Total
5	B	1/6/2018	Assigned	1		Assigned	3	3	3	1	3	1	14
6	B	1/6/2018	Assigned	1		Completed	-3	-1	-1	-1	-3	-1	-10
7	F	1/6/2018	Assigned	1		Grand Total	0	2	2	0	0	0	4
8	A	1/6/2018	Completed	-1
9	A	1/17/2018	Assigned	1
10	D	1/17/2018	Assigned	1		Sum of Count	Column Labels
11	D	1/17/2018	Completed	-1		Row Labels	1/3/2018	1/6/2018	1/17/2018	1/18/2018	1/22/2018	Grand Total
12	C	1/17/2018	Completed	-1		Assigned	3	3	4	4		14
13	E	1/17/2018	Assigned	1		Completed		-1	-3	-3	-3	-10
14	C	1/17/2018	Assigned	1		Grand Total	3	2	1	1	-3	4
15	E	1/17/2018	Completed	-1
16	B	1/18/2018	Completed	-1
17	E	1/18/2018	Assigned	1
18	A	1/18/2018	Completed	-1
19	A	1/18/2018	Assigned	1
20	C	1/18/2018	Assigned	1
21	E	1/18/2018	Completed	-1
22	E	1/18/2018	Assigned	1
23	E	1/22/2018	Completed	-1
24	A	1/22/2018	Completed	-1
25	F	1/22/2018	Completed	-1
Sheet18

Cell Formulas
Range	Formula
D2	=IF(C2="Assigned",1,-1)

 

[E. Jones]

If you record your data in a normal format you can then insert two pivot tables:


The data is in a normal format. I appreciate the time you took to assist me in this manner, however, the solution presented does not work for me. I thought I was clear on what I needed, but maybe not. I needed a way to show at a glance, how much was completed on a given date, and a rolling count throughout the day for how much has been completed.

I have a solution that is quite simple: Rather than placing an "X" in the completed column, place a "M" for Monday, "T" for Tuesday, "W" for Wednesday, "H" for Thursday, and "F" for Friday, then use a countif statement to count the number of occurrences per letter, which would of course solve the problem very easily. I was just hoping that someone might have been able to come up with a different solution.

Thank you again for the help.

 

[sheetspread]

That's a good solution too. Keeping the Xs is possible if you write a countifs instead of countif (assuming the actual data contains dates and not Mon Tue Wed etc).

The pivot table method I proposed will work, and you can quickly enter the dates with ctrl-; and status/task with a data validation drop down menu or autocomplete. Remember it for future projects if you don't feel like changing this one too much now.