Formula using SumsIF
Hi Everyone, I need assistance on a formula
I want to sum the numbers in the "Qty of Monitors" column if the Office column is "SOMA" and when the dropdowns are " Acer AND Viewsonic" are both selected in the Monitor column.
Formula I have put together=SUMIFS({Qty of Monitor}, {Office}, "SOMA", {Monitor}, "Viewsonic", {Monitor}, "Acer")
Best Answer
-
I think this should work...
=SUMIFS({Qty of Monitor}, {Office}, "SOMA", {Monitor}, and(has(@cell,"Viewsonic"),has(@cell, "Acer")))
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Answers
-
@Brittanyy Is the Monitor column a single selection or a multi selection dropdown?
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Hi @Darren Mullen its a multi selection dropdown.
-
I think this should work...
=SUMIFS({Qty of Monitor}, {Office}, "SOMA", {Monitor}, and(has(@cell,"Viewsonic"),has(@cell, "Acer")))
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen Thanks for assistance
When I plugged in the formula it showed the sum of "0" when it should be "4".
-
@Brittanyy Check that your cross sheet references are assigned correctly.
I checked this formula syntax in a sheet and it worked fine, so my guess is that either the cross sheet ranges aren't assigned properly or the other question I have is what type of column is "Office" ?
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen The Office column is a drop-down column.
But, for some odd reason the formula is populating correctly now. Thank you so much for your assistance!!!
-
@Brittanyy Great! Sometimes formulas with cross sheet references can lag a little bit or may not show the proper calculation until you refresh the sheet after saving. Probably what happened
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!