Using SUMIFS with YEAR and MONTH from single referenced column


Hi all,

I'm trying to create sheet that will sum up all revenue for a year, by month. End result should look something like this:

I'm using this formula and receiving an #INCORRECT ARGUMENT SET error:

=SUMIFS({anrev}, {vehicl}, "Site", {Scale Date}, IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 1) (this is for Jan, I change Month = 2 for Feb, Month = 3 for Mar and so on)

where {anrev} is revenue, {vehicle} is a category column and {Scale Date} is the date reference column (in form MM/DD/YY), all referenced from another single sheet.

If I remove either Year or Month functions, the formula works so

=SUMIFS({anrev}, {vehicl}, "Site", {Scale Date}, IFERROR(MONTH(@cell), 0) = 1)

provides all revenue for Jan and

=SUMIFS({anrev}, {vehicl}, "Site", {Scale Date}, IFERROR(YEAR(@cell), 0) = 2020)

provides all revenue for 2020, but the combination of the two don't seem to work. Any help would be appreciated.


Best Answer

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    I think you just need to add the {Scale Date} to both parts like ...

    =SUMIFS({anrev}, {vehicl}, "Site", {Scale Date}, IFERROR(YEAR(@cell), 0) = 2020, {Scale Date},IFERROR(MONTH(@cell), 0) = 1)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!