Forum Discussion
sumifs doesnt work because of power query
Hi All,
I've been trying to automate some regular reports using power query. The queries for the different reports are working and everything is fine, however I want to use those query tables and summarize them in my main report. The problem is that, i need to use a sumifs formula but it's not working. The formula that has always been used up until now with the old method of working is - =SUMIFS(SCRAP!$K:$K,SCRAP!$P:$P,"DTC*",SCRAP!$E:$E,"Job or Schedule",SCRAP!$B:$B,"<="&DATE(YEAR(SCRAP!$B$2),MONTH(SCRAP!$B$2),P$5)), where K:K is Value, P:P is Items, E:E is Source and B:B - dates and P5 is today's date (at the time of making the report)
I think the issue is with column B (dates). For some reason, power query seems to "bug" the column with the dates and I've tried everything and it's still not working. Tried fixing it from power query editor and on paper it should be fixed but its not. Also, tried doing that in excel but still nothing.
Any advice or a workaround would be greatly appreciated.
Thanks in advance.
3 Replies
In addition
- what do you mean under "formula not working", it returns an error or result is not as you expected?
- If some result is returned, is it higher or lower of expected one?
If in P5 is date, not day, right part of the
SCRAP!$B:$B,"<="&DATE(YEAR(SCRAP!$B$2),MONTH(SCRAP!$B$2),P$5)returns date in next century and condition is always TRUE if in column B are dates.
If in column B are texts which looks like dates, condition always returns FALSE since any text is "greater than" any number. Date in Excel is actually number.
Power Query outputs data as Excel Tables, and these behave differently from standard ranges:
• Columns may appear formatted as dates, but Excel formulas might not recognize them properly.
• Wildcards like "DTC*" can misbehave if the column isn't truly text.
• Dynamic references like SCRAP!$B:$B may not work as expected on structured tables.Below are some workarounds that would be suggested:
1. Convert Dates Explicitly in Power Query
Even if the column looks like a date, force it:
• In Power Query Editor, select the column → right-click → Change Type → Date.
• Then add a new column that extracts the date value:= Date.From([YourDateColumn])
2. Use Structured References Instead of Full Column Ranges
Instead of SCRAP!$K:$K, use structured table references:=SUMIFS(Table1[Value], Table1[Items], "DTC*", Table1[Source], "Job or Schedule", Table1[Date], "<="&P5)
Make sure Table1[Date] is truly a date column.
3. Use a Helper Column for Date Comparison
In Power Query, add a column like:= if [Date] <= Date.From(P5) then [Value] else null
Then sum that column in Excel:=SUM(Table1[FilteredValue])
4. Consider Doing the Aggregation in Power Query
If performance is an issue or formulas keep breaking, you can replicate SUMIFS logic directly in Power Query using Group By and filters:
• Filter rows where Items starts with "DTC" and Source is "Job or Schedule".
• Filter by date using Date.IsInPreviousNMonths or custom logic.
• Group and sum the Value column.In the Power Query Editor, click Home → Close and Upload → Create Connection Only to refresh the data, the SUMIFS function will be able to calculate the results normally, and it should be noted that the text format is not supported!
