Calculated field in Excel Pivot Tables

Question

I have a pivot table which lists Reference Numbers and Names in the first two columns and rows across the top and the data is a monetary amount but not present in each year. What I want to do is add a column after the Grand Total column which shows the number of years there has been a monetary amount. The screenshot below shows what I am trying to achieve. This has been created by copying the pivot table and pasting it as text and then adding the end column with a COUNTIF() field. This is OK as far as it goes but I would like to add one, or maybe more, slicers to the pivot table. In am using Office 365 on Windows 11.

Tony

Answer 1

IMHO there is no need for COUNTIF or something special, drag the year field into the values and change the calculation to Count.

If the returned numbers are too high means there are more then one row per year inside the data. In this case load the data into a Data Model, re-create the Pivot and change the calculation to Distinct Count.

That's similar but simpler as the solution from Herbert Seidenberg.

If you need further help show us your file.

Andreas.

Answer 2

Hello Aw_style_66,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

To add a calculated field in Excel Pivot Tables, you can follow these steps:

1. Click any cell inside the pivot table.
2. Go to Pivot Table Tools –> Analyze –> Calculations –> Fields, Items, & Sets.
3. From the drop-down, select Calculated Field.

However, in your case, you want to count the number of years with a monetary amount. Unfortunately, Excel Pivot Tables do not support COUNTIF or similar functions in calculated fields.

As a workaround, you could add an additional column in your source data that indicates whether there is a monetary amount for each year (1 if there is an amount, 0 if not). Then, you can use this column in your Pivot Table to sum up the values, which will give you the count of years with a monetary amount.

Remember to refresh your Pivot Table after adding new data to your source data.

I hope this helps.

Best Regards, Ibhadighi

Answer 3

Excel 365 Pro Plus with Power Pivot and Power Query.

Count active number of years.

With PivotTable and PivotChart.

No formulas, no VBA macro.

https://www.mediafire.com/file_premium/ytu5zkdicpbajv0/10_18_23.xlsx/file

https://www.mediafire.com/file_premium/50bzavzvqi3qljx/10_18_23.pdf/file

Answer 4

Many thanks everyone for your contributions. Most helpful.

Kind regards

Tony