Count of free text cells on a dashboard

Pete Cardenas

I have a projects list that is continuously being updated with a field labeled "location".

How can I count the number of instances of each location to place on a dashboard.

It's continuously changing so I cannot just put "countif" function.

Leibel S

@Pete Cardenas

Add an auto number column to your sheet called LINE-ID.

Then create a new column and add the below as a column formula.

=IF(AND(location@row <> "", MATCH(location@row, location:location, 0) = MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)), COUNTIFS(location:location, location@row))

You can then run a report on this column (where value is not blank) and show the location column next to it...

Andrée Starå

Hi @Pete Cardenas

Do you know all the possible locations, or can there be new unknown locations added?

How many locations can there be, do you think?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Pete Cardenas

Absolutely

The goal is to keep it as a free text. That way locations can be added. This is what makes it difficult. An example below:

1st floor

1st floor

Lab2

Pavilion

Lab2

Lab2

So in that instance, how can I create a dashboard that should there are 3 projects in the lab, 2 projects in the 1st floor, and one project in the pavilion?

Thanks for the assistance!

Leibel S

@Pete Cardenas

Add an auto number column to your sheet called LINE-ID.

Then create a new column and add the below as a column formula.

=IF(AND(location@row <> "", MATCH(location@row, location:location, 0) = MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)), COUNTIFS(location:location, location@row))

You can then run a report on this column (where value is not blank) and show the location column next to it...

Andrée Starå

@Pete Cardenas

Happy to help!

I saw that Leibel answered already!

Let me know if I can help with anything else!

Best,

Andrée