IF SUM OR - Need to add cells with multipliers based on a category

The first 2 lines are my test to get the sum with multipliers figured out and that works. (If better way, open to suggestions)

Tier 3 multipliers:

=SUM(Brand@row * 9, [Wine Score]@row * 6, [The Region]@row * 3, Occassion@row * 0, Personas@row * 0, Pricepoint@row * 9, Specificity@row * 3, [Distinct from the Rest of the Portfolio]@row * 4, Potential@row, Hierarchy@row * 6)

All Other Tiers:

=SUM(Brand@row * 9, [Wine Score]@row * 6, [The Region]@row * 2, Occassion@row * 1, Personas@row * 1, Pricepoint@row * 9, Specificity@row * 0, [Distinct from the Rest of the Portfolio]@row * 4, Potential@row * 9, Hierarchy@row * 6)

Line 4 is where I'm attempting the combination SUM IF OR and failing.

What I need is:

IF Tier@row = Tier 3, Use the Tier 3 string from above, otherwise use the All Other Tiers string from above.

Thank you in advance.

Best Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Try this to combine the two statements:

    =IF([Tier]@row = 3, SUM(Brand@row * 9, [Wine Score]@row * 6, [The Region]@row * 3, Occassion@row * 0, Personas@row * 0, Pricepoint@row * 9, Specificity@row * 3, [Distinct from the Rest of the Portfolio]@row * 4, Potential@row, Hierarchy@row * 6), SUM(Brand@row * 9, [Wine Score]@row * 6, [The Region]@row * 2, Occassion@row * 1, Personas@row * 1, Pricepoint@row * 9, Specificity@row * 0, [Distinct from the Rest of the Portfolio]@row * 4, Potential@row * 9, Hierarchy@row * 6))

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Oh, my apolgies... I missed that it is actually Tier 3 and not just 3.

    =IF(Tier@row = "Tier 3", SUM([Represents Brand]@row * 9, [Represents Wine]@row * 6, [Represents the Region]@row * 3, [Represents Occasion]@row * 0, [Represents Persona]@row * 0, [Represents Pricepoint (Tier)]@row * 9, Specificity@row * 3, [Distinct from the Rest of the Portfolio]@row * 4, [Brand-family Potential]@row, Hierarchy@row * 6), SUM([Represents Brand]@row * 9, [Represents Wine]@row * 6, [Represents the Region]@row * 2, [Represents Occasion]@row * 1, [Represents Persona]@row * 1, [Represents Pricepoint (Tier)]@row * 9, Specificity@row * 0, [Distinct from the Rest of the Portfolio]@row * 4, [Brand-family Potential]@row * 9, Hierarchy@row * 6))


    You need quotes around "Tier 3" to show that it is a text string.

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Try this to combine the two statements:

    =IF([Tier]@row = 3, SUM(Brand@row * 9, [Wine Score]@row * 6, [The Region]@row * 3, Occassion@row * 0, Personas@row * 0, Pricepoint@row * 9, Specificity@row * 3, [Distinct from the Rest of the Portfolio]@row * 4, Potential@row, Hierarchy@row * 6), SUM(Brand@row * 9, [Wine Score]@row * 6, [The Region]@row * 2, Occassion@row * 1, Personas@row * 1, Pricepoint@row * 9, Specificity@row * 0, [Distinct from the Rest of the Portfolio]@row * 4, Potential@row * 9, Hierarchy@row * 6))

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Oh, my apolgies... I missed that it is actually Tier 3 and not just 3.

    =IF(Tier@row = "Tier 3", SUM([Represents Brand]@row * 9, [Represents Wine]@row * 6, [Represents the Region]@row * 3, [Represents Occasion]@row * 0, [Represents Persona]@row * 0, [Represents Pricepoint (Tier)]@row * 9, Specificity@row * 3, [Distinct from the Rest of the Portfolio]@row * 4, [Brand-family Potential]@row, Hierarchy@row * 6), SUM([Represents Brand]@row * 9, [Represents Wine]@row * 6, [Represents the Region]@row * 2, [Represents Occasion]@row * 1, [Represents Persona]@row * 1, [Represents Pricepoint (Tier)]@row * 9, Specificity@row * 0, [Distinct from the Rest of the Portfolio]@row * 4, [Brand-family Potential]@row * 9, Hierarchy@row * 6))


    You need quotes around "Tier 3" to show that it is a text string.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!