Count Function - Count duplicates in multiple columns then show in chart

AppSheet AppSheet Q&A
1

Hi, all.

I have a list of business names and the owners of those businesses in columns. Any other leadership names are in two separate columns so that there are a max of 3 people who are listed as “leadership”.

For instance, if Joe Smith owns 3 companies and has another leadership role in 2 more companies, I’d like the return to tell me that Joe has roles in 5 companies.

Joe Smith = 5

I also only want to show the top 10 people with the most leadership positions.

I can do this in google sheets. https://i.imgur.com/r6NU8jy.png

How can I basically make this chart show up in appsheets?

2

Also, the spreadsheet I’m using for this app looks like this: https://i.imgur.com/1rO740T.png

3

On a Table of People, create a virtual column, with a formula similar to this:

COUNT( FILTER(
  businesses,
  OR(
    [owner] = [_THISROW] ,
    [leadership-col-2] = [_THISROW] ,
    [leadership-col-3] = [_THISROW] 
  )
) )

Then to get the top 10, create a Slice with a condition something like:

IN( 
  [key-column]  , 
  TOP( ORDERBY( people[key-column , [virtual-column-count] ) , 10 )
)

Then create a new table View on this Slice.

3 Likes