Help

PLEASE HELP WITH FORMULA (urgent)

Topic Labels: Automations Formulas
387 2
cancel
Showing results for 
Search instead for 
Did you mean: 
romilm
4 - Data Explorer
4 - Data Explorer

Hi I really need help for this and I have a deadline. So basically, I was wondering if there's a way that I can create a formula that add's up the number of time "🎯" is used across all fields and shows the result as a number in the "🎯s Used" field.

. For example for the highlighted square, since "🎯" was only used once, it would appear as 1. But for the cell above, because "🎯" was used twice across all fields it would be entered as 2.

Screenshot 2024-01-19 at 6.58.47 AM.png

 

thanks so much! 🙂

2 Replies 2

How to go about this depends on several factors.

  • Can the fields contain text besides "🎯" in the field?
  • Can "🎯" appear multiple times in the same field, and if so, should it be counted for each time, or only once for the entire field?
  • Are the different fields long text fields (as appears in your screen shot) or some other field type, such as a lookup or rollup field?

Here is one possible formula.

SUM(
  IF( FIND("🎯", {Field 1}), 1, 0),
  IF( FIND("🎯", {Field 2}), 1, 0),
  IF( FIND("🎯", {Field 3}), 1, 0),
  IF( FIND("🎯", {Field 4}), 1, 0),
  IF( FIND("🎯", {Field 5}), 1, 0)
)

 
Another consideration is that this setup of counting identical symbols across columns is much more common in spreadsheets versus databases. This setup produces "rivers of white" which can be a sign that a different database design might work better for long-term grown.

romilm
4 - Data Explorer
4 - Data Explorer

Yes, all the fields will be long text. There can be other things beside "🎯", but it will be dates written like 1/19/24. After I post, I replace the "🎯" with the date it was posted on, so "🎯" would become 1/19/24 if it was posted there today. 

With that formula, I need for it to also subtract one when the "🎯" is replaced with the date. Would that do that?

Also,  what different database design would you think would work better? I'm used to excel and a total beginner with airtable.