PLEASE HELP WITH FORMULA (urgent)

[romilm]

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.

thanks so much! 🙂

[kuovonne]

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]

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.