I wanted to use the SUMIF() formula as I would in Excel in the table below, but found that you can only pass in conditions for the column you are summing:
So in this scenario, I would only be able to add a condition such as CurrentValue>75 or something to sum the costs that are greater than $75. A 3rd optional argument in the SUMIF() formula allows the user to add a filter condition other than the summed column:
SUMIF([any column to filter], [condition], [column to sum]) where the 3rd argument is optional. If a 3rd argument is not given then the first argument is the column to sum.
Al - careful reading of the explanation looks like you’re using “testCosts.Cost” as the filter column.
Does =sumIf(testCosts.[Who Paid], testCosts.[Who Paid]=Name, testCosts.Cost) work better for you?
Like in Excel, the SumIf() function wants the criteria column first, then the actual summation column. If you omit the third argument, then it sums along the criteria column (i.e. the first argument).
Hey @chris_homburger, actually testCosts.Cost is the sum column and testCosts.[Who Paid] is the filter column. Basically I’m trying to get the total costs for each person printed out in the Summary table.
I’m familiar with SumIf() and what you described is how the formula syntax works in Excel, but that syntax doesn’t work in Coda. If you try to enter in 3 arguments in the SumIf() formula in Coda it throws an error saying “SumIf formula only takes 2 arguments.”
I’m proposing that SumIf() in Coda accepts an optional argument similar to how SumIf() works in Excel
Thanks @evan. I’ve learned from this exercise that there are multiple ways of getting to the answer. In lieu of SUMIF() you can use LOOKUP() as you described or just a regular FILTER(). I think Excel users are conditioned to use the SUMIF() formula when creating summary tables like this, but we just need to think about using LOOKUP() or FILTER() instead.
The syntax is almost the same. One thing that’s different about my formula is I have to reference the Name column instead of thisRow based on what you created. This is a whole other issue that I think has to do with the data type of the underlying column you are filtering against. In your example, the [Who Paid] column is a “Lookup From Table” column format whereas mine is just straight text. Long story short, even though it looks like you are comparing text with text, you may actually be comparing text to some other data type (which affects whether you use the column name vs thisRow in the formula).
Finally, I am curious whether a LOOKUP() is more efficient compared to a FILTER() in Coda. For large data sets in Excel (let’s say >50K rows) a VLOOKUP() is not as performant as a the INDEX/MATCH combo. In Coda, this might be an extraneous point since you are not processing thousands of rows of data (yet ).
@Al_Chen I can’t speak to the performance benefits of Lookup() vs Filter(), but making a lookup column in your list of costs does ensure data validation and allows for projections and further feature expansion in the table.
IMO, best practice in the general case would be to use the lookup column (especially because conversion from lookup to filtered text is easy, but the reverse is laborious!).
I have tried each of the above like heck and still can’t get any of the any of the options to work. But, I’m also very new to Coda and it’s formulas. Mine should be even simpler: I just want a total of everything if column a matches. I’ve tried sumif, matches, lookup, filter. What am I doing wrong in coda?
I was able to accomplish this by separating the tables with a lookup field. I also used the lookup field instead of the filter based on everyone’s recommendations here. See screenshot below. I was trying to avoid splitting the tables because I didn’t want to create a new main category every time prior to creating a sub category. But this will get the job done. Thanks everyone for the formulas/tips!
Hi @evan . Your formula saved the evening for me. I was fighting against a similar case. Muchas gracias
I would like to make you codans a suggestion. Besides your gifs, that are quite visual, at least some of us would appreciate a written example of the formula, specially the old ones with fatigued eyes. I mean, I did a screenshot and could examine peacefully the formula, but it would be easier if the formula is expressed as a text too.
).
