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!