Excel Countif function across multiple columns on a row in Coda

[Cindy_Tang]

Hello there,

I’m looking to apply the Countif formula such as in excel to the end of my row (so across multiple columns) to my Coda table, identifying how many times a particular status from a drop down menu, such as Requires Action, shows up in my row. I want to be able to track this for every status, Conditional & Remediated as well.

It seems the problem is I am unable to apply a Filter and count formula concurrently across multiple columns; either the formula will not recognize multiple columns or just the entire row OR the formula will not recognize the filter and will count all responses as the same. For instance in the example below, instead of the count for Requires action being 2 it would count as 4 (every answer).

Unfortunately I had no success with Coda’s Countif(). After many attempts this is the last formula I was able to come up with but with the partial success mentioned above:

List(thisrow.1.Status, thisrow.2.Status, thisrow.3.Status, thisrow.4.Status). Filter(“Requires Action”). Count ()

Would appreciate the help!

[Quentin_Morel]

Hi @Cindy_Tang and welcome to our amazing community !

the direct answer to your question would be to create some kind of formula like this, using boolan times 1

But to be honest, this is not the configuration where coda shows its power ! Working with columns like this is not the best, at all !

May I suggest you to have a look on the little doc I just made very quickly for you, using the fabulous lookup function, working like select list, but allowing you to make 2-ways relationship between tables…

You’ve got a main project table, with 3 columns to follow the count of status
a table with the different status to follow, and for which project they occur

And the main database will be the “status tracker”, where you create multiple rows for a given project

It’s lot of information do not hesitate to ask for further detail !

Cheers

Quentin

[Cindy_Tang]

Quentin,

You are amazing, thank you so much!

We’re essentially trying to set up trackers similar to how you’ve done. Although our full dataset is far more complex, I’ll have to play around with the suggestions you’ve offered to see if I can make sense of it all (still very new to Coda) but it’s just great to know that it can be done.

Thank you again for your fantastic and thorough guidance!

[Quentin_Morel]

No problem @Cindy_Tang I’ll let you investigate, coda is so powerful to create dashboard. If your team need additional hello to build something complex in an actual doc, do not hesitate to send a dm !

Cheers