Date and SUMIF or SUMIFS formula
I am needing help with the following formula. This following formula sums up by the month but I need it to sum by month AND year. For example, add up all January 2020 and then February 2020...etc. Any help is appreciated.
=SUMIF({Production Credits_2020 Range 12}, MONTH(@cell) = 1, {Production Credits_2020 Range 16})
Best Answer
-
@Kaylynn There are a few different ways to do this. I will show you how with your formula, and then I will show you my personal preference for doing this.
Your formula adjusted:
=SUMIF({Production Credits_2020 Range 12}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2020), {Production Credits_2020 Range 16})
My personal preference:
I use a SUMIFS instead of SUMIF. When only using a single criteria, the only difference is the syntax, but going ahead and using the SUMIFS helps keep me in the habit of using it for when I do need to add additional criteria.
I also use IFERROR statements to account for any non-date or blank cells that could be in the range. Those cells will throw an error on the MONTH and YEAR functions which in turn will throw an error for the entire formula. Replacing the error with a 0 (zero) will allow the formula to skip over those blank/non-date cells and continue counting within the range.
IFERROR(MONTH(@cell), 0)
IFERROR(YEAR(@cell), 0)
The difference in syntax with SUMIFS vs SUMIF is that with SUMIFS the range to add comes FIRST, then you follow it with range1, criteria 1, range 2, criteria 2, etc. So here is my preference of using a SUMIFS and the IFERROR statements:
=SUMIFS({Production Credits_2020 Range 16}, {Production Credits_2020 Range 12}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))
Answers
-
@Paul Newcome Are you able to help me here? (btw, I figured out how to tag you, obviously.)
-
@Kaylynn There are a few different ways to do this. I will show you how with your formula, and then I will show you my personal preference for doing this.
Your formula adjusted:
=SUMIF({Production Credits_2020 Range 12}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2020), {Production Credits_2020 Range 16})
My personal preference:
I use a SUMIFS instead of SUMIF. When only using a single criteria, the only difference is the syntax, but going ahead and using the SUMIFS helps keep me in the habit of using it for when I do need to add additional criteria.
I also use IFERROR statements to account for any non-date or blank cells that could be in the range. Those cells will throw an error on the MONTH and YEAR functions which in turn will throw an error for the entire formula. Replacing the error with a 0 (zero) will allow the formula to skip over those blank/non-date cells and continue counting within the range.
IFERROR(MONTH(@cell), 0)
IFERROR(YEAR(@cell), 0)
The difference in syntax with SUMIFS vs SUMIF is that with SUMIFS the range to add comes FIRST, then you follow it with range1, criteria 1, range 2, criteria 2, etc. So here is my preference of using a SUMIFS and the IFERROR statements:
=SUMIFS({Production Credits_2020 Range 16}, {Production Credits_2020 Range 12}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))
-
@Paul Newcome Thank you, again!!! The IFERROR formula makes sense and it is what I end up using.
-
Happy to help! 👍️
Please don't forget to mark the most appropriate response(s) as "helpful". That way others looking for a similar solution can know that one may be found here.
-
Hello Paul,
I have a similar issue where I need to return a zero to a cell to mitigate a #DIVIDE BY ZERO error.
Here is the formula: =SUMIF(Equipment@row, "Dry Van", [Rate CON Price]@row / [Loaded Miles]@row)
Thanks!
-
@Jay Schmidlapp You should be able to use an IFERROR statement. Use something along the below and replace "original formula]" with your SUMIF excluding the leading "=".
=IFERROR([original formula], 0)
-
Thanks Paul!!! Just gave it a try and PERFECT!!
Much appreciated Sir
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!