Make sum of one column in function of value of another

Guillaume B.

Hello everybody,

I did not find my answer in the different discussions, then I hope you will be able to help me.

I have a smart sheet with 2 columns:

• Part number
• Quantity

I would like to count the total of parts with a certain number. Finally, it is an addition of the quantity when the P/N is equal to a certain value.

I thought COUNTIF(S) with CONTAINS could work, but it counts only the number of cells which contains the value, it does not count the quantity.

I also thought about INDEX MATCH, but it will stop at the first match, then it will not work.

Maybe it is a combination of COUNTIF(s), CONTAINS and MATCH, because the logic is:

• Search P/N value
• when it is found, take the value in Quantity column and add it to the result

That means, COUNT function could be useful and as I need to take the value from another column, I will need to define the ROW() number of the CELL and pick up the value in the column quantity.

I also tried the function SUMIF(S), which seems to be the best one, but it does not work. I will continue with this function, because I believe that it will be possible to do something with it. The formula was

=SUMIFS(Quantity185:Quantity368, IF(CONTAINS([Part number]166, [Part number]@row), "True"), "True")

I also tried

=SUMIFS(Quantity185:Quantity368, IF(CONTAINS([Part number]166, [Part number]185:[Part number]368), "True"), "True")

[Part number]166: Part number, which is searched

Quantity185:Quantity368: Cells Range where the quantity value is add when condition is respected

[Part number]185:[Part number]368: Cells range where Part number value is searched

For now, I did not find how to do it, then if somebody has a solution, I will be very happy to learn it.

Thank you so much

PS: I forgot to tell that some Part number cells could contain two part numbers

Guillaume B.

I finally found the correct formula:

=SUMIF([Part number]$185:[Part number]$369, CONTAINS([Part number]167, @cell), Quantity$185:Quantity$369)

Like that it is working partially, because as I have sub assemblies, I will need to take the quantity of the PARENT() and multiply it to have the correct value.

Guillaume B.

I saw, that I made mistake in the formula I wrote. I did not write the complete condition (CONTAINS function shall be equal to TRUE to be summed.

The new formulas the below one, but it does not work 😥

=SUMIFS(Quantity$185:Quantity$368, IF(CONTAINS([Reference number]166, [Part number]@row) = "True", "True"), "True")

The result is #INCORRECT ARGUMENT SET, which is link to @ROW function, because if I use @CELL function, the result is #UNPARSAEBLE

I also tried ROW(@CELL), but it did not work.

The last failed test was:

=SUMIFS(Quantity$185:Quantity$368, IF(CONTAINS([Part number]@row, @cell) = "True", "True"), "True")

with the result #INVALID OPERATION

Guillaume B.

I finally found the correct formula:

=SUMIF([Part number]$185:[Part number]$369, CONTAINS([Part number]167, @cell), Quantity$185:Quantity$369)

Like that it is working partially, because as I have sub assemblies, I will need to take the quantity of the PARENT() and multiply it to have the correct value.