Displaying cumulative values in pivot table

I work for a contact center and the cases handled by employees will be surveyed. Customers would rate the interacton as Satisfied or Dissatisfied. By applying a pivot table for the data dump, we can get the day's and the month's CSAT score.

I'm looking for a way to get the cumulative values in pivot table automatically. Like the subtotals. The table has to show CSAT percentage from Day 1 to 2,3,4..... This will help me identify the CSAT trend like in a Stock price chart. Please check the attached example file. The file has two sheets one with data and the other has the Pivot table with Average formula in field settings. I'm sorry if the explanation is not clear. I will try to elaborate it further if needed.

Thanks in Advance.
Nagaraj

Hello all,

I finally worked out a solution and thought of sharing it with all. I have used Countif and Match function together to count the cumulative number of surveys. I have then used the index function inside average function. Please check attached worked file.

First, make a table to count the cumulative number of surveys.


To count cumulative number of survyes: =COUNTIF($A$1:$A$34,G3)+MATCH(G3,$A$1:$A$34,0)-1
To calculate cumulative CSAT: =AVERAGE(INDIRECT("C2"&":C"&H3))

Hope that helps someone.

Regards,
Nagaraj

Why did you tack on -1 to the formulae in cells H4:H6? I don't think the formulae in cells I4:I6 are looking at full days.

Sorry for the delayed response. Yes, you are right. -1 is redundant. It would be needed if we consider the match function reference from the cell containing the column header.

The value should be the row number.

This can be done very easily with pivot tables functionalities.

Leave the Date in ROWS
Put the Case Number in VALUES and use Count to summarize it.
Put Satisfied in COLUMNS

Then click somewhere in Values and go to Value Field Settings => Show Value as tab
chose % of Row Total in Show value as drop-down menu

Hi Kolyu,

Thank you for your response. Thank you for introducing new feature of Pivot table.

However, the values shown in second and other rows is not the accurate value. Row 2,supposed to show satisfied percetnage as 41.18% but shows 40%. See if there is something that could be worked.

Thanks again.

Regards,
Nagaraj

I have found much simpler solution for this. One can use AverageIFS function to find cumulative CSAT.

=AverageIFS (average range, criteria range, criteria)

Criteria would be date less than or equal to the date in the column left.