CountA as of

[Valavan Balasubramaniam]

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

[#2]

Use =COUNTIF(C2:C12,"") to find uncompleted.

Use =COUNTA(B2:B12) to find assigned.

[#3]

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

[#4]

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.

[#5]

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.