The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Oct 16, 2019 04:03 PM
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.
Solved! Go to Solution.
Oct 16, 2019 04:19 PM
Welcome to the community, @SunLynx! :grinning_face_with_big_eyes: The basics of your formula look okay, but there are a few issues:
SUM(number1, [number2, ...])
IF(condition, value_if_true, [value_if_false])
{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}
)
)
Oct 16, 2019 04:19 PM
Welcome to the community, @SunLynx! :grinning_face_with_big_eyes: The basics of your formula look okay, but there are a few issues:
SUM(number1, [number2, ...])
IF(condition, value_if_true, [value_if_false])
{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}
)
)
Oct 16, 2019 04:28 PM
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