Count Specific Values/Word on a Column

Column A Total (Column B)
Upwork ?
Facebook ?
Linkedin ?

I want to create formula on the second column and count those value from another table which is showing below.

if I understand this correctly you want to count how many rows of Lead Roster have Source = Facebook, Source = Linkedin, etc? if so, imo it is easier to have that formula as a column in the Source table (the table which column Source is a lookup to). Then, the column formula would be Lead Roster.Filter(Source = thisRow).Count()

another way to do it without formulas is to right click the Source column, choose Group → Group Column Along Left, and it will show the count per group

It’s showing zero or I’m doing it wrong.

can you check that the Source column in Lead Roster is of type lookup to Channel, and that the Total column in Channel is of type Number

Sorry I don’t get it.

Go ahead and share the doc! Happy to help then

Actually, looking at it closer I think that your current lead roster SOURCE column is a select list. I noticed that the color of the value ”Facebook” in the lead roster table is blue, but in your new table, that you’ve created the color of the Facebook value is red.

That leads me to believe that Your SOURCE column in the lead roster is not actually look up Collin, it needs to be a look up type that references the actual second table in your document.

Thank you: Marketing

As @Scott_Collier-Weir mentioned, you’re not using a lookup field but I think I got it to work using ContainsText() within the Filter() formula :blush:

[Lead Roster].Filter(Source.ContainsText(thisRow.Channel,true,true)).Count()

I’ve set ignoreCase and ignoreAccents to true in ContainsText() because you wrote UpWork in your select list and Upwork in the table where you want the count to appear :blush:

Now, to be honest, it really would be easier if you used a slightly different structure here :blush: :

Your Data table could store your various channels and in your [Lead Roster] table you could use lookup field to select the source.

It’ll make the channels easier to modify and manipulate on the long run (and you could easily, without potential text errors create such counts) :blush:

Edit: Note that I didn’t check how the Filter() formula would react if the text values to compare using ContainsText() has superfluous white spaces (so, it might still need some adjustments and or lead to certain errors)

Thank you for this. Everything looks confusing to me. Maybe I really need to learn the basics of formula so I can understand it better. I really appreaciate the help

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.