Forum Discussion

Copper Contributor

Apr 21, 2018

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...

Formulas and Functions

SergeiBaklan

MVP

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

Date As Of.xlsx12 KB

Valavan Balasubramaniam

Copper Contributor

Apr 23, 2018

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.

SergeiBaklan
MVP
Apr 23, 2018
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.