Forum Discussion

sheridan60's avatar
sheridan60
Copper Contributor
Sep 12, 2022

Calculated Field in Pivot

Hello, I am new to Pivot Tables so please bear with me. I am trying to determine monthly costs for each job, represented by a WO#. My timesheet data is downloaded from Replicon but it does not conta...
excel
sheridan60's avatar
sheridan60
Copper Contributor
Sep 12, 2022

DexterG_III I am provided with a file from HR/Payroll. Apparently this is my only option. I had to join the two tables myself. I just figured out that I can use VLOOKUP and add the total for each line item to the spreadsheet I was given. I now have a total per work order per entry, but cannot get a total per work order overall. I'm not sure a pivot table is gonna work for me.

DexterG_III's avatar
DexterG_III
Iron Contributor
Sep 12, 2022

sheridan60 While Vlookup will pull a value from a single cell (the first match in the lookup table - a single workorder entry), using sumif will pull values from several cells.  Using sumif should allow you to pull the total by workorder rather than workorder entry.  

  • sheridan60's avatar
    sheridan60
    Copper Contributor
    Sep 12, 2022
    Can I use sumif/sumifs if I have 3000 different identifiers, without entering detail for each one?
    • DexterG_III's avatar
      DexterG_III
      Iron Contributor
      Sep 12, 2022

      sheridan60 As long as you have the common keys in both tables, yes.  For example, this formula uses both username and Workorder Number.  It's just one formula which, if entered in a table object, will spill to all rows in that table.  

       

      =SUMIFS(Table3[Hours],Table3[Workorder],[@Workorder],Table3[Username],[@Username])

       

       

  • sheridan60's avatar
    sheridan60
    Copper Contributor
    Sep 12, 2022
    Thanks. I'll work on that.