CODEWITHSUNDEEP
google-sheetspivot-table
sam
sam

Reputation: 10064

Correct syntax for a Calculated field in a Google sheets pivot table?

Ive got 4 columns that i want to summerise as part of a calculated field in a pivot table.

In Sheet 1 i have the raw data and in Sheet 2 i have the pivot table.

For the calculated field i want to sum the values for the following columns named "Alpha", "Beta", "Charlie", "Delta". In this example the columns are L,M,N,Z.

To generate this calcualted feild value do this ive been trying the following ="Alpha"+"Beta"+"Charlie"+"Delta" and also =L+M+N+Z.

But they both through errors.

Im sure its something wrong with my syntax, but cant work out what.

Any ideas ?

Upvotes: 0

Views: 22202

Answers (5)

April Urban
April Urban

Reputation: 23

I recently also had a great deal of difficulty with calculated fields in Google Sheet pivot tables. I could not get any of these solutions to work for me, bouncing between different types of errors.

Ultimately, I found this resource on the query function the most helpful. I found the query function to be significantly easier to work with than calculated fields in pivot tables. You can basically produce your own pivot table with the GROUP BY statement and use arithmetic functions to calculate fields. It is similar to writing SQL and seems to work well with different data types. In my opinion is it easier to just create a query table than to figure out and correct what's going wrong with a pivot table.

Here's a great resource on how to use the query function: https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/

Upvotes: 0

Niklas Johansson
Niklas Johansson

Reputation: 64

You need to reference your columns with single-quote:

='Alpha'+'Beta'+'Charlie'+'Delta'

Google sheet pivot only allows column name from the source data to be used as reference in the calculated field or a direct cell reference, e.g. A1.

Upvotes: 3

smac89
smac89

Reputation: 43078

From this post, you can also use ArrayFormula. So you can do this simply:

=ArrayFormula(Alpha+Beta+Charlie+Delta)

Upvotes: 1

tdarn
tdarn

Reputation: 81

This also worked for me:

=sum('Alpha', 'Beta', 'Charlie', 'Delta')

Good luck :)

Upvotes: 6

warbirdn
warbirdn

Reputation: 83

I believe in my testing it would be:

=sum(L,M,N,Z)

Upvotes: 1

Related Questions