Count Occurrences in a Column

AppSheet AppSheet Q&A
1

I feel like I am pretty close to a solution, but I could use some help closing the gap.

I have an app that allows our staff to collect data for a contractor to complete a quote. Once the data collection is complete, the staff member which contractors they want to deliver the data to using an ENUMLIST. I am trying to determine how many times we have delivered quote opportunities to a contractor.

I am using the following expression to try and get the count, but since multiple contractors can be selected, I need to use an asterisk (*) wild card before or after the contractor name since it could be anywhere in the string. How do I apply wildcards when using an expression like this? Also, will this accomplish what I am looking for?

=COUNT(FILTER("Product", [Assigned Leads] = QCN[CompanyName]))

For clarity:

  • Product is the table where the data is stored
  • Assigned Leads is the column storing the selected contractors (ENUMList)
  • QCN[CompanyName] is the table/column with each of the contractors we partner with

I hope I’ve given enough information. Searching this forum has gotten me this far. I appreciate any help to get me the rest of the way!

2

Where are you trying to use this expression?

1 Like
3

@Steve

Sorry, should have included that. I am attempting to create a virtual column that provides the count.

1 Like
4

A virtual column in what table?

What table is the Assigned Leads column in?

1 Like
5

Adding a Virtual Column to the QCN table. The QCN table is the list of all participating QCN members.

The Assigned Leads is in the Project table where all the data is collected.

1 Like
6

Try this:

COUNT(FILTER("Product", IN([_THISROW].[CompanyName], [Assigned Leads])))
2 Likes
7

This did it! Thank you, Steve for all your help!

1 Like