I'm stumped when trying to aggregate data from three columns

Hi! I’m trying to aggregate multilingual survey answers using a formula.

I will eventually need this format of formula for more complex data, but I am stumped at the beginning part of getting at least three statements to all say ‘YES’ if there is a ‘yes’ in one column or a ‘Si’ in another or a ‘容易 - Yes’ in another.

This formula works :IF({Was it easy for you to order a meal through the current texting system? CHINESE}=‘容易 - Yes’, ‘YES’)

But then I want to build upon it by adding the conditions IF or even OR it doesn’t work. For example, this does not work:

IF({Was it easy for you to order a meal through the current texting system? CHINESE}=‘容易 - Yes’, ‘YES’) OR(IF(
{Was it easy for you to order a meal through the current texting system?}=‘Yes’,‘YES’))

Does anyone have any ideas of how to make what I want to happen/guidance on what I’m doing incorrectly?

Thanks so much in advance.

The general structure of the formula you’re looking for is

IF(
   OR({Field A} = "Yes", {Field B} = "Si", {Field C} = "容易 - Yes"),
   "Yes"
)

If you wanted to use nested IF()s, that format would look like:

IF(
   {Field A} = "Yes",
   "Yes",
   IF(
      {Field B} = "Si", 
      "Yes", 
      IF(
         {Field C} = "容易 - Yes",
         "Yes"
      )
   )
)

The first formula should be all you need, but you said it may need to get more complicated later so it may be good for you to see how to properly nest IF() statements.

4 Likes

Thanks so much Kamille, that worked. Can you help me with one more step for more complex ones? So I’m trying to add these two formulas in one.

1)IF(OR({Gender}=“Female”, {Gender CHINESE}=“女性”,{Gender SPANISH}=“mujer”),“Women”)
2) IF(OR({Gender}=“Male”, {Gender CHINESE}=“男性”,{Gender SPANISH}=“masculino”),“Man”)

I’ve tried a couple of combinations and it wont seem to work. Ex:

  • CONCATENATE(IF(OR({Gender}=“Female”, {Gender CHINESE}=“女性”,{Gender SPANISH}=“mujer”),"Women”)),IF(AND({Gender}=“Male”, {Gender CHINESE}=“男性”,{Gender SPANISH}=“masculino”),“Man”)))

Do you have any suggestions of what I should do to combine those two formulas?

Your CONCATENATE() technically should work if you use OR() instead of AND() and put your parenthesis in the right places.

Here is where you would nest your IF()s. The format of a nested IF() statement is shown above. If you wanted to keep your formulas as simple as possible that would be:

IF(
   OR({Gender}="Female", {Gender CHINESE}="女性",{Gender SPANISH}="mujer"),
   "Women",
   IF(
      OR({Gender}="Male", {Gender CHINESE}="男性",{Gender SPANISH}="masculino"),
      "Man"
   )
)

Note: Grammatically, “Women” should probably be “Woman” since you’ve written “Man” and not “Men”

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.