Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

If/Switch Function Help

Topic Labels: Formulas
Solved
Jump to Solution
1372 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I am trying to write a function that allows me to add a set of numbers if a specific criteria has been met.
EX:
SUM (IF {Category 1} = “widget”, value {Cost 1})+(IF{Category 2} = “widget”, value {Cost 2})+(IF{Category 3} = “widget”, value {Cost 3})+(IF{Category 4} = “widget”, value {Cost 4})

(I’m not even sure if an IF function can be used this way.)

I also don’t know if I should be using a rollup table or something else, given that this data would be on a separate sheet.

Any help is appreciated. Thank you.

1 Solution

Accepted Solutions

Welcome to the community, @SunLynx! :grinning_face_with_big_eyes: The basics of your formula look okay, but there are a few issues:

  1. The SUM function is not correctly formatted. You’ve got + symbols in-between the different pieces you’re trying to add, but they should be separated by commas instead. The basic SUM format is: SUM(number1, [number2, ...])
  2. Your IF functions are also incorrectly formatted. The parentheses should go around the contents: IF(condition, value_if_true, [value_if_false])
  3. For all functions, there should be no space between the function name and the opening parenthesis.
  4. Where you’re inserting the value, you’ve put “value {Cost 1}”, “value {Cost 2}”, etc. The “value” prefix is an invalid specifier. If the listed fields ({Cost1}, {Cost 2}, etc.) contain dollar values, all you need is the field reference.

With those changes, here’s the result:

SUM(
    IF(
        {Category 1} = "widget",
        {Cost 1}
    ), IF(
        {Category 2} = "widget",
        {Cost 2}
    ), IF(
        {Category 3} = "widget",
        {Cost 3}
    ), IF(
        {Category 4} = "widget",
        {Cost 4}
    )
)

See Solution in Thread

2 Replies 2

Welcome to the community, @SunLynx! :grinning_face_with_big_eyes: The basics of your formula look okay, but there are a few issues:

  1. The SUM function is not correctly formatted. You’ve got + symbols in-between the different pieces you’re trying to add, but they should be separated by commas instead. The basic SUM format is: SUM(number1, [number2, ...])
  2. Your IF functions are also incorrectly formatted. The parentheses should go around the contents: IF(condition, value_if_true, [value_if_false])
  3. For all functions, there should be no space between the function name and the opening parenthesis.
  4. Where you’re inserting the value, you’ve put “value {Cost 1}”, “value {Cost 2}”, etc. The “value” prefix is an invalid specifier. If the listed fields ({Cost1}, {Cost 2}, etc.) contain dollar values, all you need is the field reference.

With those changes, here’s the result:

SUM(
    IF(
        {Category 1} = "widget",
        {Cost 1}
    ), IF(
        {Category 2} = "widget",
        {Cost 2}
    ), IF(
        {Category 3} = "widget",
        {Cost 3}
    ), IF(
        {Category 4} = "widget",
        {Cost 4}
    )
)

Aha! I knew there was a lot wrong with the formatting (partly so people would understand what I was looking for) but there is plenty I would have missed.

Thank you. I will try this.

Edited to add: This worked like a dream, thank you @Justin_Barrett