SUMIFS Issue
I'm having a hard time evolving the following SUMIF formula into a SUMIFS formula so I can account for the month and the year. Here is my SUMIF formula:
=SUMIF(MONTH([Billing 1 Date]@row), =3, [Billing 1]@row) + SUMIF(MONTH([Billing 2 Date]@row), =3, [Billing 2]@row) + SUMIF(MONTH([Billing 3 Date]@row), =3, [Billing 3]@row) + SUMIF(MONTH([Final Billing Date]@row), =3, [Final Billing]@row)
Please help!
Thank you!
Best Answer

My mistake! I left a bunch of commas in there that are screwing it up. You'd need those commas for SUMIFS or COUNTIFS, but they will break IF functions! Here is where I left the errant commas in each IF:
Fixed:
=IF(AND(MONTH([Billing 1 Date]@row) = 3, YEAR([Billing 1 Date]@row) = 2022), [Billing 1]@row, 0) + IF(AND(MONTH([Billing 2 Date]@row) = 3, YEAR([Billing 2 Date]@row) = 2022), [Billing 2]@row, 0) + IF(AND(MONTH([Billing 3 Date]@row) = 3, YEAR([Billing 3 Date]@row) = 2022), [Billing 3]@row, 0) + IF(AND(MONTH([Final Billing Date]@row) = 3, YEAR([Final Billing Date]@row) = 2022), [Final Billing]@row, 0)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

The syntax is a bit different with SUMIFS. With SUMIFS, you list the range that you want to add, followed by criterion range 1, criterion 1, criterion range 2, criterion 2, etc.
It looks like you may not need that though. In fact, you might not need SUMIF or SUMIFS for what you are doing. Let me see if I can translate your formula into English:
If the month value in [Billing 1 Date]@row is 3, add the value in Billing 1 on this row... to what? SUMIF and SUMIFS usually apply to a range, not a single cell.
It looks like you really just need a string of four IF/AND statements that get added together. Each one says "IF these two conditions are true, give me the value in this cell, otherwise give me 0". So in the end, you're adding 4 values together to get a total.
=IF(AND(MONTH([Billing 1 Date]@row), =3, YEAR([Billing 1 Date]@row), =2022), [Billing 1]@row, 0) + IF(AND(MONTH([Billing 2 Date]@row), =3, YEAR([Billing 2 Date]@row), =2022), [Billing 2]@row, 0) + IF(AND(MONTH([Billing 3 Date]@row), =3, YEAR([Billing 3 Date]@row), =2022), [Billing 3]@row, 0) + IF(AND(MONTH([Final Billing Date]@row), =3, YEAR([Final Billing Date]@row), =2022), [Final Billing]@row, 0)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Than you, Jeff! For some reason i'm still getting an invalid data type error?

Also, given the context of what I'm trying to accomplish maybe I'm going about it in a obscure way. I'm attempting total up a number of project billings that land in the same month. So that I can build a proper spending curve. Like this:
This is my data set:

My mistake! I left a bunch of commas in there that are screwing it up. You'd need those commas for SUMIFS or COUNTIFS, but they will break IF functions! Here is where I left the errant commas in each IF:
Fixed:
=IF(AND(MONTH([Billing 1 Date]@row) = 3, YEAR([Billing 1 Date]@row) = 2022), [Billing 1]@row, 0) + IF(AND(MONTH([Billing 2 Date]@row) = 3, YEAR([Billing 2 Date]@row) = 2022), [Billing 2]@row, 0) + IF(AND(MONTH([Billing 3 Date]@row) = 3, YEAR([Billing 3 Date]@row) = 2022), [Billing 3]@row, 0) + IF(AND(MONTH([Final Billing Date]@row) = 3, YEAR([Final Billing Date]@row) = 2022), [Final Billing]@row, 0)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

AWESOME, thank you so much, Jeff! This worked perfectly.
Help Article Resources
Categories
Check out the Formula Handbook template!