SUMIFS by Month
Good day...trying to work on a formula to bring back a summed value for each month and having no luck!
I have 100 rows with all differing completed dates and a dollar values...sounds simple? Its not.
Best Answers
-
My next guess is that you have either blanks and/or non-date values somewhere within your date range.
=SUMIFS({SAVING}, {DATE}, IFERROR(MONTH(@cell), 0) = 1)
-
Yes you can...
=SUMIFS({SAVING}, {DATE}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2021))
Answers
-
Are you working on the same sheet or a different sheet? Are you getting incorrect results or error messages? Are you able to provide a screenshot of the source data with sensitive/confidential information removed, blocked, and/or replaced with mock data as needed?
-
Hi Paul...I thought you may be the one to help :-)
Invalid operation.
2 screenshots attached.
-
You have your ranges switched in the SUMIFS. When using that particular function, the range to sum comes first and is then followed by your criteria range/criteria.
-
Darn...still getting the invalid operation?
=SUMIFS({SAVING}, MONTH(@cell), 1, {DATE})
-
Try this...
=SUMIFS({SAVING}, {DATE}, MONTH(@cell) = 1)
-
Nope...Invalid data type now
=SUMIFS({SAVING}, {DATE}, MONTH(@cell) = 1)
-
My next guess is that you have either blanks and/or non-date values somewhere within your date range.
=SUMIFS({SAVING}, {DATE}, IFERROR(MONTH(@cell), 0) = 1)
-
YES! Giddy up! I was playing with the "iferror" but in over my hear!
I appreciate all the help...have an awesome holiday season, stay safe!
-
hi @Paul Newcome just an additional question on this, can I add the year to this formula? I'm returning values in December that are still on my data sheet from 2020
=SUMIFS({SAVING}, {DATE}, IFERROR(MONTH(@cell), 0) = 12)
-
Yes you can...
=SUMIFS({SAVING}, {DATE}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2021))
-
Perfect functioning exactly as expected...thanks again Paul
-
Trying to get 2nd Qtr to show the number of Reviews, with the 0's or blanks for May and June.
-
Help Article Resources
Categories
Check out the Formula Handbook template!