Forum Discussion
CountA as of
Hi Team,
Need a help for this,
I have a columns called "Assigned" and "Completed". They both are getting updated multiple time each day as new assigned and completed.
When I create report for each day I want to calculate total of all those are not completed (ie, to be completed). I used =COUNTA which is showing same for each day. Not sure whether =COUNTIF can be used. ANy idea how can it be done.
Eg:
| Assigned | Completed | |
| Data as of 20/04 | 14-Apr | 14-Apr |
| 15-Apr | 10-Apr | |
| 16-Apr | 15-Apr | |
| 17-Apr | ||
| 18-Apr | ||
| 19-Apr | ||
| 20-Apr | 21-Apr | |
| 21-Apr | 20-Apr | |
| 22-Apr | 19-Apr | |
| 23-Apr | ||
| 24-Apr | ||
| Data as of 21/04 | 22-Apr | 22-Apr |
| 29-Apr | 21-Apr | |
| 30-Apr | ||
| 17-May | ||
| 5-May | 20-Apr | |
| 4-May | ||
| 3-May | 18-Apr | |
| 22-Apr | ||
| 24-Apr | 16-Apr | |
| 26-Apr | ||
| 23-Apr | ||
| Data as of 22/04 | 22-Apr | 22-Apr |
| 29-Apr | 21-Apr | |
| 30-Apr | 22-Apr | |
| 17-May | 21-Apr | |
| 5-May | 20-Apr | |
| 4-May | ||
| 3-May | 18-Apr | |
| 22-Apr | 22-Apr | |
| 24-Apr | 16-Apr | |
| 26-Apr | ||
| 23-Apr |
Based on above sample data I would like to have the report in below format,
| Date | Bucket (To be completed |
| 20/04/2018 | 5 (to be completed) /11 (total assigned as of 20/04/2018) |
| 21/04/2018 | 11 (to be completed) /22 (total assigned as of 21/04/2018) |
| 22/04/2018 | 14 (to be completed) /33 (total assigned as of 20/04/2018) |
| 23/04/2018 | 14 (to be completed) /33 (total assigned as of 20/04/2018) Besause we havent reached 23/04 /2018 yet |
| 24/04/2018 | 14 (to be completed) /33 (total assigned as of 20/04/2018) Besause we havent reached 24/04 /2018 yet |
| 25/04/2018 | 14 (to be completed) /33 (total assigned as of 20/04/2018) Besause we havent reached 25/04 /2018 yet |
| 26/04/2018 | 14 (to be completed) /33 (total assigned as of 20/04/2018) Besause we havent reached 26/04 /2018 yet |
Use =COUNTIF(C2:C12,"") to find uncompleted.
Use =COUNTA(B2:B12) to find assigned.
Hi Valavan,
It looks like you have merged cells in very left column with date in unclear format ("Date as of <date>"). At the same time you'd like to have summary table for above dates. Not obvious task.
Perhaps it's easier to calculate the summary directly in you main list, e.g. using SUBTOTAL on count
=SUBTOTAL(2,$C$5:C27) =C28-SUBTOTAL(3,$D$5:D27)
If collect the same result in separate table we shall identify each record in above list or at least subtotals. If against each subtotal it will be the date we may return result as
=INDEX(D:D,MATCH(H7,B:B,1)) & " (to be completed) /" & INDEX(C:C,MATCH(H7,B:B,1)) & " (total assigned as of " & TEXT(LARGE(B:B,1),"yyyy-mm-dd") & ")"
Please see attached
Thanks Sergei,
the merged column on the far left does not exist in my data. I have just created to show the difference of data entry date.
What I am trying to do is, calculate all from todays date and display. Once data refreshed tomorrow, today's data remains same and tomorrow data display as accumulated (Today + Tomorrow).
Actually data coming from SharePoint list refreshed regularly but need to produce report for each day.
FYI -
I have formulae worked out to refresh everyday and showing week worth of date on the left. When the new week comes it refresh automatically.
Okay, I see. And when you refresh the list how do you know which records were added today, yesterday or day before yesterday?
If no such marks you may COUNT/COUNTA entire list by the end of each day and copy/paste results as value.
