• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

pivot need help for creating calculated field which will ...

A

aria

New Member
Hi,

How are you all.

I want to create a calculated field which will do a sumproduct of all activity fields with the AHTs and result to be shown in pivot with the name production hrs.
Tried doing it but could not ....Need expert help with this. Would be grateful if this can be done. I have attached the file with query, AHT and required data.

Query
: Want Production Hrs calculation to be done using calculated field in the below mentioned pivot. Formula for production hrs calculation should give result as sumproduct of all activities (highlighted in yellow) with the AHT (highlighted in red).
regards,
Aria
 

Attachments

  • Pivot question.xlsx
    14.4 KB · Views: 2
AFAIK - SUMPRODUCT can't be used in Calculated Field

You can accomplish what you want by having 2 tables, one for AHT and another for Activities and using Data Relationships. However, you need to organize your data for Activity to match database format.

If above is not feasible, there are workarounds.

1. Calculated field with Sum function
=SUM(Activity1*11.14,Activity12*10,Activity13*2,...)

2. As you did in your sample, add calculation column to the table and build PivotTable from there.
 
You must log in or register to reply here.
Back
Top