Skip to main content

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

  • October 15, 2021
  • 3 replies
  • 40 views
K

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.

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

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.

K
kuovonne
Justin_Barrett
K
  • Kayla_Marquez_V
  • Author
  • New Participant
  • October 18, 2021

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?

Kamille_Parks11
Forum|alt.badge.img+27

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”

Terms & ConditionsAccessibility statement