SUMIFS Issue

krollshow

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!

Jeff Reisman

@krollshow

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)

Jeff Reisman

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)

krollshow

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

krollshow

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:

Jeff Reisman

@krollshow

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)

krollshow

AWESOME, thank you so much, Jeff! This worked perfectly.