Forum Discussion
Sumifs with Custom Excel Data type from power Query and using dot notation
I am just experimenting with Custom Excel Data Types and dot notation.
I was trying to come up with a equivalent to sumifs without any luck. In my example I use fake data and I am trying to summairze charges by team color and department
Here is a drop box link to my spreadsheet
Here is the expected outcome
| Yellow | Red | Green | Purple | Orange | Red | |
| Feet | 1,384,281 | 1,067,303 | 884,288 | 1,112,979 | 1,005,634 | 1,167,165 |
| Hands | 1,267,428 | 1,262,445 | 827,956 | 963,616 | 1,041,856 | 902,571 |
| Hip | 946,395 | 948,135 | 955,020 | 799,842 | 1,014,142 | 829,546 |
| Knee | 991,524 | 1,072,020 | 953,689 | 1,139,318 | 1,218,487 | 1,001,327 |
| Spine | 933,123 | 1,373,616 | 910,488 | 795,726 | 860,861 | 1,019,545 |
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
4 Replies
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-conceptsBest 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.
Hi
Here is a drop box link to my spreadsheet => Unfortunately there's no link in your initial post - Not necessarily your mistake as this site currently (& randomly ???) drops ex. file attachments and hyperlinks are bugging as well
Could you try again please?
SUMIFS doesn’t work directly with custom Excel data types, even though dot notation makes it look possible, because Excel still treats those fields as records rather than normal ranges. The simplest fix is to pull the needed values into helper columns using dot notation (for example, team color, department, and amount) and then run SUMIFS on those columns. If you’d rather avoid helper columns, a PivotTable built from the Power Query output is usually the cleanest and most reliable way to create the summary you’re after.
