PivotTable sub-totals with zero cells

Nikki

New member
Joined
Mar 27, 2013
Messages
4
Reaction score
0
Points
0
My sub-totals are not adding columns that have empty cells or error values formatted to display 0. How do I format the sub-totals to treat these cells as 0?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Are you getting a count instead or an error?
 

Nikki

New member
Joined
Mar 27, 2013
Messages
4
Reaction score
0
Points
0
I'm getting a SUM but the value displays as "-".
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Is it possible to see what you mean with a sample attachment?
 

Nikki

New member
Joined
Mar 27, 2013
Messages
4
Reaction score
0
Points
0
This is a small portion of the file. I can't attach the entire file, but if you need to see the formatting I could try to duplicate in a smaller version. As you can see below, the sub-total of L1 does not appear because (I think) there are empty cells in the column. I can't remove the rows because there are values in other columns. The sub-total of L2 sums the rows in the category correctly.


L1 28,522 28,734
L1 - -
L1 - -
L1 21,001 23,755
Sub - -
L2 73,127 54,012
L2 57,854 49,510
Sub130,981 103,522
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
If your numbers in the table are formatted as numbers, then not sure without seeing an actual sample file.
 

Nikki

New member
Joined
Mar 27, 2013
Messages
4
Reaction score
0
Points
0
The table was formatted for numbers however, as I was preparing a sample to attach I discovered the problem which was primarily related to duplicates.
 
Top