Skip to main content

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.

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, rnumber2, ...])

  2. Your IF functions are also incorrectly formatted. The parentheses should go around the contents: IF(condition, value_if_true, uvalue_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

Reply


Terms & Conditions