Coda's equivalent to Excel's SUMIF, COUNTIF, and AVERAGEIF

#1

Quick Answer: Add a filter to your formula and then add SUM(), COUNT(), or AVERAGE() to the end of your formula

If you’ve tried using SUMIF or COUNTIF in Coda, you may have run into a few snags. I hear you, I was frustrated too when I couldn’t figure out why a basic SUMIF formula wouldn’t work in Coda. First off, you can only SUM or COUNT if the predicate is a number (not text) and you have to use the tricky CurrentValue to get the number. I know, a little crazy. Excel seems so much easier right? Look how simple this is:

It’s pretty similar with COUNTIF and AVERAGEIF as you know. So how about Coda? It’s different, yes, but simpler in it’s own way. So to get the SUMIF to work the same way as in Excel (in this case summing all the “Counts” where the “Fruit”=“Watermelon”):

If this was a contest to write the shortest formula to get the sum, Excel would totally win. But in Coda, there aren’t any fuzzy references to D12:D20 or other cell ranges, you use words that describe the data instead of cell references. The biggest difference with this formula is that you chain the SUM function to the data.

1 Like

CountIf with dynamic content
Enhancement request - conditional aggregation functions
#2

I’m doing something wrong…

I put in a dataset (it’s from a public recordset of a public university but I’ve removed names). I set it up like you did but when I put in the formula it throws an exclamation point error. I’ve found that I can’t type the brackets: it removes them. Here’s a screenshot when I put the brackets in:

Then, after pressing Enter and checking, they’re gone:

And the formula doesn’t work:

0 Likes

#3

Hey @Michael_Olenick, thanks for posting! I realized that the example I posted did not have the best column names. If you notice in my dataset, the second column name that contains the values is called “Count.” That’s why after I apply the filter to my table, I reference the “Count” column and then the SUM() formula.

In your example, the column you want to reference is “ColC,” so the formula should be:

=[MyData].filter(ColA="AC2 Adjunct Faculty").ColC.Sum()

Let me know if that works for you! Regarding the brackets getting removed, when you click back into the formula do the brackets show up again? Whether there are brackets in this case shouldn’t affect the formula.

0 Likes

#4

Voila! That did it! Thank you!!

I realized counting text values (ex; in my example how many rows are AC2 Adjunct Faculty) is easy enough: just add another column, fill it with 1’s, and count that. Kinda’ clunky but not the worst workaround.

Also tested that it’s possible to string multiple filter’s together which is much easier than the excel ifs functions. Finally, using Median() allows a MEDIANIF (IFS) function, something Excel hasn’t managed to add – despite an obvious need – literally in decades.

My test dataset is 1587 rows. Has anybody tested at what row count performance begins to drop off? I’d imagine that reserving lots of server memory per user would make running the program cost prohibitive; it’s OK for early testing but wouldn’t scale financially. Of course, you could eventually allow users to rent large memory instances when needed. Maybe it won’t matter - I’d guess that most (the vast majority) of people will probably want to create smaller documents or they’d switch to a traditional database.

0 Likes

#5

Awesome. Yup stringing multiple conditions in the filter is definitely a pro for Coda, and easier to build relative to nested IF functions in Excel!

0 Likes

#6

Today @mallika helped me with the same issue. Curious to see how Multiple Conditions and/or List of Values can be used to build a Filter. TIA.

1 Like

#7

@Kailash_Attal regarding multiple conditions in the filter, are you referring to using the AND operator as explained here?

0 Likes

#8

Yes, for the most part, however, I have a list of values in the same column that I want to be able to use. Current way is to OR it with multiple values for the same column, I am looking more like if it exists in a list of values.

0 Likes

#9

Gotcha. Would you be able to share a screenshot of what you are trying to do? I’m having a hard time understanding the exact layout of your columns and data.

0 Likes