Forum Discussion
Sumifs with Custom Excel Data type from power Query and using dot notation
I found a solution by googling. AI gave me the answer, but I could not find the original source(s)
Use Sumproduct or sum & filter. If someone can help me identify the original source I will give them credit.
https://www.google.com/search?q=sumifs+using+custom+data+type&rlz=1C1GCEB_enUS1066US1066&oq=sumifs+using+custom+data+type&gs_lcrp=EgZjaHJvbWUqBggAEEUYOzIGCAAQRRg7MgYIARBFGDwyBggCEEUYPDIGCAMQRRg80gEIMTgwOGowajeoAgCwAgA&sourceid=chrome&ie=UTF-8
TL;DR
Hi BALDACCOUNTANT,
You can’t use SUMIFS directly on fields inside a custom Excel data type via dot notation. Dot notation works for single values, but not for array evaluation.
To summarize charges by team color and department, you need to either expand the fields into normal columns or use Power Query Group By or PivotTables.
Full Answer
You won’t be able to use SUMIFS directly against fields inside a custom Excel data type via dot notation. At present, Excel does not support array evaluation of structured fields in built‑in functions like SUMIFS. That’s why your attempts failed.
- Expand the fields into normal columns If your custom data type has fields like Color, Department, and Charges, expand them into separate columns. Then you can use a standard SUMIFS:
=SUMIFS(Charges, TeamColor, "Yellow", Department, "Feet")
This works exactly as expected.
2. Use Power Query Group By (preferred) Since your data already comes from Power Query, you can do the aggregation there:
- In Power Query, go to Home → Group By
- Group by Department and TeamColor
- Aggregate the Charges column with Sum
- Load the result back to Excel as a table or PivotTable
This produces the exact summary you showed (Feet/Hands/Hip/Knee/Spine vs. Yellow/Red/Green/etc.) without complex formulas.
3. PivotTable option If you want to stay in Excel:
- Insert a PivotTable from your expanded table
- Put Department in Rows
- Put TeamColor in Columns
- Put Charges in Values (Sum)
That gives the same outcome as your expected table.
Microsoft Documentation
Custom functions and data types concepts: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-data-types-concepts
Best practices for custom functions in Excel: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-best-practices
Bottom Line Dot notation is great for pulling single values, but it doesn’t yet work with SUMIFS across arrays. The practical solution is to expand the fields or use Power Query Group By or PivotTables for summarization.