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
-
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))
-
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
-
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 THANK YOU!!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!