CODEWITHSUNDEEP
excelexcel-formula
alejnavab
alejnavab

Reputation: 1212

Pivot Table can't SUM in Data Model

I have an Excel sheet with two tables (that are indeed tables), one with a single-column with employee names, and the other which is an array with the start and end time of each employee and the corresponding worked time that is automatically computed; this second table has various entries for each employee.

Now, I want to create a Pivot Table, included in the Data Model, that sums all the worked hours of each employee. But Excel gives an error when I try to use SUM (or AVERAGE, or any other function that requires numbers): "We can't summarize this field with Sum because it's not a supported calculation for text data types."

But if I create the Pivot Table with the "Add this data to the Data Model" checkbox unchecked, then there's no error. I've checked that the value of the cells in the worked time of each employee is indeed a number, and not a text.

Here's a screenshot of everything:

enter image description here

How can I get the Pivot Table created with the Data Model checked, to SUM the worked hours of each employee?

Upvotes: 0

Views: 1576

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

My error message is different. "We can't summarize this field with Sum because it's not a supported calculation for **date** data types."

I have read, but not confirmed, that a different tool (the Power Pivot tool) is used to create the Pivot Table when you add to the data model. And the capabilities are not the same.

If that is what you really see, then, after you add it to the data model:

  • Power Pivot
  • Manage
  • Select the Worked time column and change the data type to Decimal Number
  • Now create the Pivot table from either within Power Pivot, or close Power Pivot and create it from the Excel worksheet.
    • You should be able to SUM the worked time.
    • You may want to format that Value as [hh]:mm using the Number Format option in Value Field Settings.

enter image description here

EDIT: Since you seem to be reluctant to use Power Pivot, you can also get the same result with a formula. Entered into a single cell, the results will SPILL.

Change the table name from Table2 to whatever your times table is:

=LET(
    empl, UNIQUE(Table2[Employee]),
    wt, BYROW(empl, LAMBDA(arr, SUMIF(Table2[Employee], arr, Table2[Worked time]))),
    VSTACK({"Employee", "Sum Worked time"}, HSTACK(empl, wt))
)

You will need to format the Time worked column appropriately.

Upvotes: 1

Related Questions