I have a budget with dates of items purchased. I have tried many different ways to sort the amounts spent for each year using “sumif” or “sumif & year”.
I keep getting either syntax errors or not enough arguments for the formula. This should be pretty simple, but I have spent hours on this issue and cant resolve it. Any suggestions would be appreciated. Thanks!
—–
John Cirino
MacMost Q&A Forum • View All Forum Questions • Ask a Question
How Do I SUMIF Dates/Years?
Comments: 3 Responses to “How Do I SUMIF Dates/Years?”
Comments Closed.
So is the problem that you have one column that has dates, and you want to use SUMIF on that base on the year of the date? Since you can't match a year (a number like 2018) with a date (like 1/1/2018) you can't do it directly.
So the way to do it is to use another column. So if column A has the dates, then column B uses the function YEAR to extract the year from the date. So A1 is 1/1/2018 and B1 is =YEAR(A1). Then use SUMIF to compare the values in column B, not the dates in column A.
When you have it working, you can always hide column B if you like.
Thanks for the reply Gary.
Can I use the "year" function in the formula since it nets me the year from the date? I guess not since I have tried it. What good is the "year" function, if it doesn't extrapolate the year in the formula?
Thanks
John: Do you mean can you use the YEAR inside the SUMIF? The problem there is that you are not providing a single value in the parameters. It is SUMIF(test-values,condition,sum-values). The YEAR function takes a single date value and converts it to a number representing the year. But if you tried SUMIF(YEAR(test-values),condition,sum-values) then you are not feeding YEAR a single date, but a range of cells which it doesn't handle. That's why you need the extra column to do the YEAR function, and then use that in the SUMIF. It is a fairly common technique.