Forum Discussion
SUMIFS function not working with date range
I am attempting to calculate debits from a ledger within monthly date ranges. However, I am only getting a "0" as a result. As far as I can tell, I am doing everything correctly. I have formatted all of my cells as dates, and the return value as a number. I have successfully used this formula in the past, and even tried to copy the formula that is working properly over to this workbook, but have had no success. Any thoughts? (sample file attached)
6 Replies
=NUMBERVALUE([@Date])
=SUMIFS(Table3[WD],Table3[Spalte1],">="&B3,Table3[Spalte1],"<="&C3)
I added a new column to Table3 with the number value of column date and applied the above SUMIFS formula and it works in my spreadsheet.
- Also, my main ledger is in another worksheet in this same workbook. I attempted to change the master ledger and add the column that you suggested, but I only received a "VALUE!" error in the cells. When I copied the ledger data over to the worksheet that has my formula, it seems to work. I don't think I understand why that would make a difference
- as you sort of realized, the problem was not with your formula but the dates in the table being text and not numerical/dates. Even though you set the formatting of that column to be "DATE" that doesn't convert text to date values, it just says to excel to show a VALUE in a date format. For example if you have "abc" in a cell and set it to format "DATE" it doesn't magically become some date. Same goes for "11/8/2021" because that is text (in quotes). As shown above the function NUMBERVALUE or better yet DATEVALUE can be used to convert text that looks like a date into a VALUE excel understands. Alternatively you can highlight that column and use Text to Columns (under the Data tab) to convert the text to actual values.
- Thank you for the help. I'm not sure why that would work and my original formula wouldn't, but I can't argue with good results! I did notice that when I formatted the Table3[Date] column as a date, the alignment didn't match what I would expect to see in a cell formatted that way (left align instead of right), but I wouldn't think that would matter as long as Excel is reading them all as dates. Do you perhaps know what was wrong with my original formulas?
