Sum Received Quantity Based on two criterions.
Good Morning All,
I am working on creating a formula that will sum my "Received Quantity" column when two criterions are met within a DIFFERENT data sheet. These would be criterion from two separate columns needing to be met so that I can get a total sum of those items together. I was just wondering if this would lean more towards a =SUM formula or maybe =COUNTIF/=COUNTIFS formula. Thank you for any help you all can give me.
Best Answer

@Matthew R This info was perfect. Ok, here's how you do this:
=SUMIFS({Received Quantity}, {Commodity Code}, Part@row, {Type of Material}, HAS(@cell, "Electropolished"))
This will sum the values in the Received Quantity column where the Commodity Code equals the Part on the row, and where the multiselect Type of Material column includes the value "Electropolished". (HAS will search for a value inside a multiselect column that matches the given text string exactly.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

How are those criteria in the remote sheet related to the rows you'd want to SUM?
The least complicated way to do this might be to add some kind of indicator column on your main sheet that tells you if the criteria are met in the remote sheet, and use that as the criteria for SUMIF to use.
The more info you share here (column names, types, of data, how they relate to one another) the better help you'll get.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Picture one is the metric sheet where I am trying to track my received quantities of my two specified criterion columns from the data sheet in the second picture.
Picture two is the data sheet where I am collecting my data.
I am trying to SUM my "Received Quantity" column on my main metric sheet in picture one IF AND ONLY IF my "Commodity Code" column states "9400 FG  HEALTH CARE" and when my "Type Of Material" column states "Electropolished", hopefully this was a little more clear and what I am after. I have a picture of the formula I have started in the first picture as well, you will find it in the "Received Quantity" column in that picture.
Thank you for your response Jeff and any and all help you can provide!

@Matthew R This info was perfect. Ok, here's how you do this:
=SUMIFS({Received Quantity}, {Commodity Code}, Part@row, {Type of Material}, HAS(@cell, "Electropolished"))
This will sum the values in the Received Quantity column where the Commodity Code equals the Part on the row, and where the multiselect Type of Material column includes the value "Electropolished". (HAS will search for a value inside a multiselect column that matches the given text string exactly.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

That is exactly what I was after in regards to this formula. I plugged in the formula into my sheet and it worked perfectly! Very much appreciate your help!
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives