SOLVED

SUMIFS with dates in table

Iron Contributor

Hello,

Can anyone explain why the below formula yields 0 as a result please? (I want the sum of all quantities of the last year.)

bartvana_0-1617380478490.png

 

4 Replies
best response confirmed by bartvana (Iron Contributor)
Solution

@bartvana 

Please use "<="&[@date] instead of "<=[@date]", etc. Second one literally returns the text inside the quotes which definitely couldn't be found. 

Thank you, that definitely clears up one problem. I adapted accordingly, but I must still be making some stupid mistake because the value still remains zero although the sum shouldn't be:

bartvana_0-1617383300543.png

(edit: added the spreadsheet)

OK, got it myself, the type of the date column in the Power Query that makes the table was "text", when I changed it to "date" it worked.

@bartvana 

Yes, in general it's the best practice to define proper types returning the table to Excel sheet or to other source.

1 best response

Accepted Solutions
best response confirmed by bartvana (Iron Contributor)
Solution

@bartvana 

Please use "<="&[@date] instead of "<=[@date]", etc. Second one literally returns the text inside the quotes which definitely couldn't be found. 

View solution in original post