SUMIFS by Month

John Littler

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.

Paul Newcome

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)

Paul Newcome

Yes you can...

=SUMIFS({SAVING}, {DATE}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2021))

Paul Newcome

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?

John Littler

Hi Paul...I thought you may be the one to help :-)

Invalid operation.

2 screenshots attached.

2020-12-23_15-10-44.jpg

2020-12-23_15-03-22.jpg

Paul Newcome

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.

John Littler

Darn...still getting the invalid operation?

=SUMIFS({SAVING}, MONTH(@cell), 1, {DATE})

Paul Newcome

Try this...

=SUMIFS({SAVING}, {DATE}, MONTH(@cell) = 1)

John Littler

Nope...Invalid data type now

=SUMIFS({SAVING}, {DATE}, MONTH(@cell) = 1)

Paul Newcome

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)

John Littler

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!

Paul Newcome

Happy to help. 👍️

John Littler

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)

Paul Newcome

Yes you can...

=SUMIFS({SAVING}, {DATE}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2021))

John Littler

Perfect functioning exactly as expected...thanks again Paul

Paul Newcome

Happy to help. 👍️

Sue Hill

Trying to get 2nd Qtr to show the number of Reviews, with the 0's or blanks for May and June.

Paul Newcome

@Sue Hill Try this...

=COUNTIFS(CHILDREN(), @cell = 0)