Sum Received Quantity Based on two criterions.

Matthew R

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.

Jeff Reisman

@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 multi-select Type of Material column includes the value "Electropolished". (HAS will search for a value inside a multi-select column that matches the given text string exactly.)

Jeff Reisman

@Matthew R

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.

Matthew R

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!

Jeff Reisman

@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 multi-select Type of Material column includes the value "Electropolished". (HAS will search for a value inside a multi-select column that matches the given text string exactly.)

Matthew R

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!