Can you use column summaries in a formula?

Hey everyone, I’m brand new to coda and am blown away by it!!! I’m still familiarizing myself with the table formulas and I was wondering if it’s possible to use the count values in the column summary in a formula?

I’m tracking some job opportunities in a table, and want to tally up the number of engagements and the number of hires, and in the end, write a formula to find the % of the hire rate.

In both engagement/hire columns, I simply have a Yes/No, as seen in the attached screen.

This has to be an easy solution! Thanks in advance for any help/tips.

1 Like

Hi @Derek_Rungsea :slight_smile:
Welcome to the community :grin:

What you are trying to get is super easy to calculate, the ‘trick’ is to not refer to the ‘count at the botton of the table’ but to re-create that number :slight_smile:

For example, if the table is called “table1” you can use ‘table1.count’ and see the total of row :slight_smile:

Thanks to coda here starts the interesting part :slight_smile:

Let think for a moment that you are curious about % of hiring in different categories, what you have to write is ‘table1.filter(category=thisrow.category).count’ and only the row that satisfy your condition are counted :slight_smile:

It’s a simple concept and it gives you a lot of freedom in your calculi, if it’s a little bit hard don’t worry, share a dummy doc here and i can build some demo implementations of this concept :slight_smile:

Hope this help in some ways :smiley:

Keep making, experimenting and posting here :grin:

Hey Derek,

That’s awesome to hear! Welcome to the Coda Community!

To answer your question, are you referring to the Summarize feature in the column options (mentioned in this help article)? If so, you wouldn’t be able to include a formula there.

For custom summarizations, you can create a named formula to display below the table by hitting the “=” sign on a new line. For percentages, I divide the number of Hired = Yes rows by the # of table rows. Here’s a copy of the formula for reference. Hope this helps!

Concatenate( Quotient([RSVP Example].Filter([Y/N]).Count(),[RSVP Example].Count()).Product(100).RoundDown(1), "%")

2 Likes

@Mario, That’s what I figured, and was able to do this, thanks for your help!

@Renita_Mwangachuchu, thank you for your suggestion as well, as I think this is a good approach!

2 Likes

@Renita_Mwangachuchu, So i’ve been playing around with your method, and I cannot for the life of me figure out what is going wrong in my formula. I haven’t nailed down the structure yet, and it’s pretty frustrating (right now I have a very strong love/hate relationship with coda LOL, until I get over the learning curve).

I’ve created a quick easy table to show. I’ve gotten the engagement rate correct, but cannot get the hire rate to calculate 25%. I either get 0 or 100

Would you be able to find what I’m missing here?

Thank you!

@Derek_Rungsea You are missing the concept of filter, you have to use the filter in another way.

Now you are using .filter like that

Screenshot 2020-11-08 at 09.52.13

You are saying “from this table project filter me the project table.hired=yes”

Formula editor should give you some type of “what?” but anyway

This is the correct method:

Screenshot 2020-11-08 at 09.51.49

This work like that

Filter from the table project all the row in which “hired=yes” and then count them

The approach of @Renita_Mwangachuchu uses canvas formula that doesn’t work like in the table “thisrow.something” but you have to use “currentvalue.something”

The learning curve in coda is a little hard but don’t worry you will get it :slight_smile:
Just try to “decompose” the formula in every piece and try it like that, then you can put it together again!

2 Likes

Ah yes, I see now, that worked! I’ll be continuing with learning the formula logic.

Thanks for the help!

1 Like