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...
=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.
- So using this logic (excuse the pun), I might be able to import the date data, convert it to a numerical date for the formula's purposes, but then have the "displayed" formatting change the appearance of the data back to a date format while still maintaining the usefulness of the data in my formula?
- 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?
