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:
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
