Forum Discussion

BALDACCOUNTANT's avatar
BALDACCOUNTANT
Copper Contributor
Dec 18, 2025
Solved

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 summ...
excel
  • BALDACCOUNTANT's avatar
    BALDACCOUNTANT
    Dec 22, 2025

    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

     

    Olufemi7​ Lorenzo​ sophieturner​ 

sophieturner's avatar
sophieturner
Brass Contributor
Dec 18, 2025

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.

Resources