# Make sum of one column in function of value of another

Options
edited 06/21/23

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

Options

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.

• edited 06/21/23
Options

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

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