Counting the number of times a word appears in a list

Hi All,

I am trying to count the number of times a word appears in a list - pictured below. Does anyone know what the formula would be for that? Thank you for the help!

Best,
Hannah

Screen Shot 2021-11-01 at 10.18.39 AM

Hi @Hannah_Krumholz :blush:

How do you get your list in the table ? … And what is the formula in your Column 3 :blush:

@Hannah_Krumholz
if you share the link of your doc, we can help you better but i think you can do it with this formula:

Lookup(thisTable,thisTable.reaserch method , thisRow.reaserch method).Count()

I have a main table that has a bunch of rows with associated Research Methods

Then I created a list of all those methods and now in the new table I want to count how many times the designated research method is listed in the list
Screen Shot 2021-11-01 at 11.25.29 AM

You can ignore column three because that’s the column I was trying to write the formula in to count the list

From what I can see in your last screenshot, I think the easiest way would be to change a little bit your list formula to get an actual list vs. a “ToTexted” list of lists :blush: (as from what I can see, this is what seems to be happening there :blush: )…

So instead of using List() you could use ListCombine() (to flatten the list of lists) and then count what needs to be counted :blush: .

Depending if you need the list to be visible or not you could use a formula looking like this in your List column :

[Research Library Master...].[Research Method].ListCombine().[Research Method]

This should give you your list of all methods :innocent:.

Then, in your Column 3 you could have a formula looking like this :

thisRow.List.Filter(CurrentValue = thisRow.[Research Method]).Count()

If you want an “all-in-one” formula (i.e.: without showing the list) you could use something like this :

[Research Library ...].[Research Method].ListCombine().[Research Method].Filter(
  CurrentValue = thisRow.[Research Method]
).Count()

I didn’t get the time to produce a sample, sorry :innocent: … But if you need more explanations, if something is not clear enough (or if it doesn’t work :sweat_smile:), don’t hesitate to come back to this post :blush: .

In the meantime, I hope this helps :innocent: !

1 Like

That worked!!! Thank you so much!

1 Like

Ah :grin: ! I’m very glad to know it worked :grin: !

I’ve seen this desired pattern many times and the solution is always a bit complex. In my firm, we do this all the time - looking for basic counting metrics to render conclusions and ideally in charts. To overcome this complexity, I decided to create a custom Pack - a simple formula that given a list of terms (or numbers), counts the occurrences and places the results in an adjacent column.

Countify(
  <term>,
  <list>
)

The pack itself is amazingly simple; Coda has found a way to make me look like a genius.

/*

   ***********************************************************
   SmartPacks: Countify
   Copyright (c) 2021 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************

*/

//
// import the packs sdk
//
import * as coda from "@codahq/packs-sdk";

//
// create the pack
//
export const pack = coda.newPack();

//
// add the smartify phone number
//
pack.addFormula({
  
  //
  // pack name and description
  //
  name: "Countify",
  description: "A formula that counts occurrences of values in a field.",

  //
  // result type
  //
  resultType: coda.ValueType.Number,

  //
  // parameters
  //
  parameters: [
    coda.makeParameter({
      type: coda.ParameterType.String,
      name: "<value>",
      description: "The value to test in a column."
    }),
    coda.makeParameter({
      type: coda.ParameterType.StringArray,
      name: "<column>",
      description: "The column containing the values to be tested for occurrences."
    }),
  ],

  //
  // execute CountifySelect
  //
  execute: function ([thisText, thisList]) {
    return thisList.filter(x => ( x.toString().toLowerCase().indexOf(thisText.toString().toLowerCase()) > -1 )).length;
  },

});
2 Likes