# Count and average in a pivot?

Hello experts,

In excel 2007, how can I make dynamic count and average in rows and columns in a pivot table?

See the attached example

vba is ok - as long as it's as dynamic as the pivot

Regards, Raahaugen Mappe1.xls Mappe1.xls
###### Who is Participating?

Commented:
Try this:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rngData As Range
Dim rngColSummary As Range, rngRowSummary As Range
Dim lngRows As Long, lngCols As Long, lngRow As Long, lngCol As Long
Set rngData = Target.DataBodyRange

With rngData
lngRow = .Row
lngCol = .Column
lngRows = .Rows.Count
lngCols = .Columns.Count
' this is the first row of the summary under the table
Set rngColSummary = .Offset(lngRows).Resize(1, lngCols - 1)
Set rngRowSummary = .Offset(, lngCols).Resize(lngRows - 1, 1)
End With

' clear rest of sheet
' all rows below table
Range(Cells(lngRow + lngRows, "A"), Cells.SpecialCells(xlCellTypeLastCell)).Clear
' then columns to the right
Range(Cells(1, lngCol + lngCols), Cells(1, Columns.Count)).EntireColumn.Clear

With rngColSummary
' colour both summary rows
' need to extend by one column to include the grand totals bit
.Resize(2, .Columns.Count + 1).Interior.ColorIndex = 3 ' red
.FormulaR1C1 = "=COUNT(R" & lngRow & "C:R[-2]C)"
' add average formula in row below and set number format
With .Offset(1)
.FormulaR1C1 = "=average(R" & lngRow & "C:R[-3]C)"
.NumberFormat = "0.00"
End With
' add grand totals in column to right
.Offset(, lngCols - 1).Resize(, 1).FormulaR1C1 = "=sum(RC" & lngCol & ":RC[-1])"
.Offset(1, lngCols - 1).Resize(, 1).FormulaR1C1 = "=average(RC" & lngCol & ":RC[-1])"
End With

With rngRowSummary
.Resize(, 2).Interior.ColorIndex = 5

.FormulaR1C1 = "=COUNT(RC" & lngCol & ":RC[-2])"
With .Offset(, 1)
.FormulaR1C1 = "=average(RC" & lngCol & ":RC[-3])"
.NumberFormat = "0.00"
End With
End With

End Sub
0

Commented:
You can't make them part of the pivot, so you need formulas. Add this code to the worksheet (after removing the other pivottable)
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rngData As Range
Dim lngRows As Long, lngCols As Long, lngRow As Long, lngCol As Long
Set rngData = Target.DataBodyRange
With rngData
lngRow = .Row
lngCol = .Column
lngRows = .Rows.Count
lngCols = .Columns.Count
With .Offset(lngRows).Resize(1, lngCols - 1)
.FormulaR1C1 = "=COUNT(R" & lngRow & "C:R[-2]C)"
.Offset(1).FormulaR1C1 = "=average(R" & lngRow & "C:R[-3]C)"
.Offset(, lngCols - 1).Resize(, 1).FormulaR1C1 = "=sum(RC" & lngCol & ":RC[-1])"
.Offset(1, lngCols - 1).Resize(, 1).FormulaR1C1 = "=average(RC" & lngCol & ":RC[-1])"
End With
With .Offset(, lngCols).Resize(lngRows - 1, 1)
.FormulaR1C1 = "=COUNT(RC" & lngCol & ":RC[-2])"
.Offset(, 1).FormulaR1C1 = "=average(RC" & lngCol & ":RC[-3])"
End With
End With

End Sub
0

Author Commented:
Great - is it possible to extend the code to clear the unused fields after update? let's say first the pivot shows 4 dates, and it counts just fine. Then instead I want to look at only 3 dates, this makes the pivot 1 column smaller, and thus the last column with the average from before tableUpdate is still standing, though it is no longer relevant?

But great work really.. in the workbook where this will be used, I have both many more fields as well as rows of data to calculate from, so I hope to make this work efficiently - not least because many people will be using it.

Regards
0

Author Commented:
Oh and one more thing - the average shows many decimals. Is it possible from vba as well to make the cells show only 2 decimals?
0

Author Commented:
I will be playing around with some background colors as well, so please explain to me where I can set cell specifications on the rows and columns listing count and average?

By specifications I mean colors, format and so on.
0

Commented:
Here is a fun-n-learn version of your file. Well ! it does what you ask for... ;)

ps. If you are keen to proceed with this kind of a solution, you should be able to dynamiclly populate the formulaes and achieve your objective. It is possible.
Mappe1.xls
0

Author Commented:
Rorya - that is exactly what I'm talking about!!!

Just one more thing: Whenever I add one date (makes the pivot wider or taller), excel keeps asking me, if I want to replace the content of the destination cells? How can I remove that pop-up every time?

The code does exactly what it should!

gbanik, your formula works, but doesn't fulfill my wish. To make it do that, the formula will have to dynamically move with the pivot table.

the best -
0

Commented:
You can't unless you are using code to cause the refresh (in which case you can set Application.DisplayAlerts = False)
0

Author Commented:
I see - perfect! Full points

gbanik I will give you a chance to post something that works - not that you have to! But if you want the points, you are welcome.

Tomorrow I will close the question.

Rorya, you're the best !

Cheers
0

Commented:
Raahaugen... please go ahead and close the question. Though it is possible to do what I suggest... but may not be worth the effort. Rorya's solution is good for you.
0

Author Commented:
As said, this sulution simply works. Sorry I didn't close the q earlier.

Thanks for reminding.

Regards, raahaugen
0

Author Commented:
Hey rorya!

Please help me rewrite the code to only write the summary under the pivot, and don't write no summary to the right?

The case is, I have a pivot with only one column, and then underneath the column I want the count and the average number.

Regards, Raahaugen
0

Commented:
That would be a new question... :)
0

Author Commented:
Roger!

:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.