Create Excel Pivot Table Calculated Field With a Count

You can create calculated fields in a pivot table, to add your own formulas.

However, a calculated field always uses the SUM of another field, even if that field is displayed as a COUNT.

To get correct results, you can add a new field to your source data, to act as a counter. Then, use the new field in the calculated field, and its SUM will be used, for the results that you expected.

Watch this video to see how to create a pivot table, add a new counter field to the source data, and create a calculated field using the counter field.

Visit this page to download the sample file.

Video Timeline
00:00 Introduction
00:38 Build Pivot Table
01:59 Create Calculated Field
02:50 Change Number Format
03:11 Calculated Field Problem
04:03 Add New Field to Source Data
05:28 Create New Calculated Field

Instructor: Debra Dalgleish, Contextures Inc.
#ContexturesExcelTips #ExcelQuickTips

[dennisfarmer]

This is the only place I could find information on how to do this specific Calculated Field. Good work, thank you!

[yoelleny]

when doing this pivot for multiple tabs, the calculated field options doesn't work anymore, please help

[pipo]

Debra, you are a wonderful person. In this video I found exactly what I needed. Everything is always well explained in detail. I am grateful for you.

[youtube]

For some reason the total of calculated field doesnt show the right number. 🤔 can anyone help please

[ChrisKhachatourians]

Thank you. This is a very nice video of the concept.

[benaycock]

Exactly what I was looking for. Thank you!!

[MC-xpxd]

How to proper count row of the sum total if i was inserted the calculated field in pivot ?

[camshaft]

Why did you start with the date field? Just made it more complicated

[iffi]

Thank you. But how to calculate only positive units in pivot table

[SpinozasPsyche]

Finally someone explaining what was wrong with my formula.

[faaizahmedkazi]

Thanks, spent hours figuring out the mess

[pmsocho]

Thanks!
Helper columns very often come in handy :)

[sughosh]

Thank you so much. Very good information. I could not get this information anywhere else. I was looking so hard for this.

[longdorji]

How to create Calculated field between two items with sum value and average value. For instance total Sales of Month over Average Sales of Month. I have one column data with Sales in Sum value and another column with Sales in average value in my pivot table. Now i want to create third column using calculated field for Sum Sales/ Average sales. Would appreciate your

[ForvoQuizlet]

So you can't calculate a count? Bummer. Anyway, this got me to where I needed to be - thanks much!

[kaiserthegreat]

Oddly, with Google Sheets you can create a calculation involving counts and it doesn't treat it like sums.