Date and SUMIF or SUMIFS formula

Kaylynn

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})

Paul Newcome

@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))

Kaylynn

@Paul Newcome Are you able to help me here? (btw, I figured out how to tag you, obviously.)

Paul Newcome

@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))

Kaylynn

@Paul Newcome Thank you, again!!! The IFERROR formula makes sense and it is what I end up using.

Paul Newcome

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.

Jay Schmidlapp

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!

Paul Newcome

@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)

Jay Schmidlapp

Thanks Paul!!! Just gave it a try and PERFECT!!

Much appreciated Sir

Paul Newcome

Happy to help. 👍️