# Pivot Table - sum AND average in the same field

#### velocitygrrl

##### New Member
Hi there,

I am SURE this must be possible, but I can't for the life of me make it work.

I have a massive pivot table with three different categories of source data listed and subtotaled by a few different fields. Of the three categories of source data, I want two of them to SUM in the subtotals and one of them to AVERAGE.

I know I can change these settings under "Field Settings" but every time I do so, it wants to change all three to SUM, all three to AVERAGE, or all three to SUM and AVERAGE, but i can't seem to sum two and average one, nor can I hide just the subtotals I don't need.

I hope someone can make sense of this simple yet complicated question!!

Many thanks.
v.grrl.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Norie

##### Well-known Member
Can't you just add the data field again and then set average/sum/whatever?

#### velocitygrrl

##### New Member
I don't think I follow.

Of my three source categories, I can properly set each of them to either sum or average, but the subtotals/totals seem to only want to be one or the other..

#### Norie

##### Well-known Member

What I meant was that if you had a data item that was a sum then you could add that item again but set it to average.

#### velocitygrrl

##### New Member
yeah this is a tough one to explain. can i attach a screenshot somehow?

i have three fields across the top, and three types of data
it looks kinda like this:

field 1 field 2 field 3
number1 5
number2 100
% 5%
number1 1
number2 100
% 1%
subtotal field 3 number1 6
subtotal field 3 number2 200
subtotal field 3 % 6%

where you see number1 and number2, i am successfully showing a sum, where you see % i am successfully showing an average

subtotal field 3 is my problem -- i can make all three appear as a sum or an average, but can't show the number1 and number2 as sums and % as an average..

does this make any sense at all??
[/img]

#### Norie

##### Well-known Member
You can post an example of your data and required result using Colo's HTML Maker?

#### velocitygrrl

##### New Member
ok - it looks like some spaces are causing issues here, but hopefully it's enough to give you the idea. the bit highlighted in yellow is my trouble area..!
2005 Dec 13 - Ad Text Performance.xls
CDEF
4KeywordMatchingKeywordData12/1/05
6Clicks5
7CTR29.41%
8domainkeyImpressions3
9Clicks0
10CTR0.00%
11senderidImpressions16
12Clicks1
13CTR6.25%
14senderidImpressions6
15Clicks0
16CTR0.00%
Sheet1

#### Norie

##### Well-known Member
Can you repost after reading the FAQ regarding the HTML maker, the nbsp is a known problem?

Also it might be an idea if you posted an example of the raw data the pivot is based on.

That would give us something to work with.

Replies
1
Views
1K
Replies
0
Views
324
Replies
2
Views
259
Replies
4
Views
401
Replies
6
Views
330

1,171,138
Messages
5,873,996
Members
433,016
Latest member
LorenzAsurza

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back