Nested IFs and SWITCH function

[SunLynx]

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.

[Justin_Barrett · Accepted solution]

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}
    )
)

[SunLynx]

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