Multiple cell formula to calculate order QTY
Hi All,
I'm trying to create a query that will divide the value of a column depending on the value selected in another.
For context, we need to order a certain number of ink cartridges based on the number of badges we need to print. However, the number of ink cartridges also depends on the type of badge we are printing.
Example:
 If you select the "XXL badge (Black Ink)" we need to divide the total number of badges "QTY" by 400 (Colour) to calculate the number of "Black ink cartridges" we need.
 If you select the "XXL badge (Colour Ink)" we need to divide the total number of badges "QTY" by 2000 (Colour) to calculate the number of "Colour ink cartridges" we need.
As the value in the "Badge type" changes per row, I need a query that will do this automatically based on the "badge type" value will apply the correct sum.
To write the query out simply:
 If "Badge Type" = XXL (Black Ink) divide the "QTY" of badges by 400
 If "Badge Type" = XXL (Colour Ink) divide the "QTY" of badges by 2000
 If "Badge Type" = CR80 (Black Ink) divide the "QTY" of badges by 1000
 If "Badge Type" = CR80 (Colour Ink) divide the "QTY" of badges by 200
Cells and values below (screenshot)
Would really appreciate the help to:
 Confirm this is possible? (Ha)
 Help me with the initial query/formal string
Thanks
Dave
Best Answer

Hi David,
Try this:
=IF([Badge Type]@row="XXL (Black Ink)",[Badge QTY]@row/400,IF([Badge Type]@row="XXL (Colour Ink)",[Badge QTY]@row/2000,IF([Badge Type]@row="CR80 (Blank Ink)",[Badge QTY]@row/1000,IF([Badge Type]@row="CR80 (Colour Ink)",[Badge QTY]@row/200,""))))
The "" at the end states that if none of the criteria are met, it will display a blank cell. Hope this helps!
Best,
Heather
Answers

Hi David,
Try this:
=IF([Badge Type]@row="XXL (Black Ink)",[Badge QTY]@row/400,IF([Badge Type]@row="XXL (Colour Ink)",[Badge QTY]@row/2000,IF([Badge Type]@row="CR80 (Blank Ink)",[Badge QTY]@row/1000,IF([Badge Type]@row="CR80 (Colour Ink)",[Badge QTY]@row/200,""))))
The "" at the end states that if none of the criteria are met, it will display a blank cell. Hope this helps!
Best,
Heather

Many thanks Heather, this really help and more importantly, worked!

Fantastic! Happy to help. Have a great weekend.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 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!