Make sum of one column in function of value of another
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
Best Answer

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.
Answers

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

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 377 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!