Skip to main content

PLEASE HELP WITH FORMULA (urgent)

  • January 19, 2024
  • 2 replies
  • 27 views
R
Forum|alt.badge.img+2

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! 🙂

2 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • January 19, 2024

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.

R
Forum|alt.badge.img+2
  • romilm
  • Author
  • New Participant
  • January 20, 2024

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.


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.

Terms & ConditionsAccessibility statement