SUMIFS in sheet summary field

neisdorfer

Hi everyone - I finally figured out the weird syntax that sheet summary fields require for sumifs courtesy of an answer to one of the questions for a different user, but I still can't get my formula to work.

=SUMIFS([Planned Expense]:[Planned Expense], [Event Type]:[Event Type], @cell = "Moments that Matter", [Event past]:[Event past], @cell = "current"). It would also be helpful if I could get this to work:

=SUMIFS([Planned Expense]:[Planned Expense], [Event Type]:[Event Type], @cell = "Moments that Matter", OR([Event past]:[Event past]= @cell "current",[Event past]:[Event past] = @cell "future")

neisdorfer

Think I might have figured out the problem - some of those columns have column formulas, I think I remember something about that interfering with sheet summaries. So weird.

MedaUser

Do any of your rows have both "Moments that Matter" in the Event Type column AND "Current" in the Event past column? If not, then this would be why it's not summing them. Therefore, the solution would need to be written as such:

=SUMIF([Event Type]:[Event Type], @cell = "Moments that Matter",[Planned Expense]:[Planned Expense]) 
+ SUMIF([Event past]:[Event past], @cell = "current", [Planned Expense]:[Planned Expense]) 
+ SUMIF([Event past]:[Event past], @cell = "future", [Planned Expense]:[Planned Expense])

neisdorfer

Thank you for that thought! I'm in the process of building this out so it's possible that I don't yet have any that meet all the criteria. I would have expected it to just return $0 in that case though.

I tried the formula you posted as well as a variation where I added parentheses around all of the individual sumif statements -

=(SUMIF([Event Type]:[Event Type], @cell = "Moments that Matter", [Planned Expense]:[Planned Expense])) + (SUMIF([Event past]:[Event past], @cell = "current", [Planned Expense]:[Planned Expense])) + (SUMIF([Event past]:[Event past], @cell = "future", [Planned Expense]:[Planned Expense]))

Both give me that same invalid operation error. I wonder if there's some other characteristic of the data that's causing a problem. Because this sheet is a series of parent events and child expenses under the event I've had to add a few helper columns throughout to make other formulas work. I'm sure it's something there that's causing the problem.

neisdorfer

Think I might have figured out the problem - some of those columns have column formulas, I think I remember something about that interfering with sheet summaries. So weird.