# SUMIFS by Month

Options
✭✭✭✭

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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

Yes you can...

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

«1

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

Invalid operation.

2 screenshots attached.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

Darn...still getting the invalid operation?

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

• ✭✭✭✭✭✭
Options

Try this...

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

• ✭✭✭✭
Options

Nope...Invalid data type now

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

Yes you can...

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

• ✭✭✭✭
Options

Perfect functioning exactly as expected...thanks again Paul

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

@Sue Hill Try this...

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!