SUMIF() filtering a condition other than column to be summed?

#1

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.

1 Like
Coda's equivalent to Excel's SUMIF, COUNTIF, and AVERAGEIF
Sumif using two variables?
Summary/Aggregate Views
#2

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).

Does that make sense?

1 Like
#3

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 :grin:

#4

Got it, @al_chen! I misread and thought that was the current syntax. That makes much more sense for a SumIf().

1 Like
#5

Thanks for raising @Al_Chen - I’ve seen a ton of “summary table” use cases of Coda recently. We’ve done a few things to simplify this a bit with Coda (I’ve captured your sumif request as well!), namely creating an easy entry point to create summary tables in the first place, and using Lookup() formula in the formula chain to accomplish the sumif case:
First:

Then:

1 Like
#6

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.

Using FILTER()

Using LOOKUP()

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 :grin:).

#7

@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!).

1 Like
#8

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?

#9

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!