Forum Discussion

Marco_Sabau's avatar
Marco_Sabau
Copper Contributor
May 09, 2023

Calculated field in pivot table referencing table in another sheet

Hi,


I have a question regarding calculated fields in a pivot table.
I have a 8 column table in a sheet that I used to make a pivot table in another sheet. I want to add a field that is the conditional sum obtained from two of the columns from the table in the first sheet.
I tried using this formula:

 

 

=SUMIF(TabellaInterventi[Confermato?];"1";TabellaInterventi[Valore intervento])

 

 

When I click "Add" nothing happens and "TabellaInterventi[Confermato?];" becomes gray. I still don't know what that means as it doesn't provide any sort of indication.
I tried another formula, that not only references the table, but also the sheet:

 

 

=SUMIF(Interventi!TabellaInterventi[Confermato?];"1";Interventi!TabellaInterventi[Valore intervento])

 

 

To no avail, but now I get a syntax error. I also tried surrounding the column names with "", but it doesn't work.
As far as I know (and I know very little) the syntax is correct, but the formula just doesn't work.

 

I'd be glad to receive any kind of help, thank you.

 

Also let me know if you need more details to help me

  • Hi Marco_Sabau 

     

    if I understood correctly, you wanted to add your formulas to a calculated field in a pivot table?

     

    Unfortunately, that's not possible. In a calculated field of a pivot table, you cannot refer to a cell or a cell range of your workbook. Also, you can use only very basic arithmetic calculations like + - / * or functions that do not refer to a cell or a range of the workbook.

     

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor

    Hi Marco_Sabau 

     

    if I understood correctly, you wanted to add your formulas to a calculated field in a pivot table?

     

    Unfortunately, that's not possible. In a calculated field of a pivot table, you cannot refer to a cell or a cell range of your workbook. Also, you can use only very basic arithmetic calculations like + - / * or functions that do not refer to a cell or a range of the workbook.

     

Resources