Make sum of one column in function of value of another

Options
Guillaume B.
Guillaume B. ✭✭
edited 06/21/23 in Formulas and Functions

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

  • Guillaume B.
    Guillaume B. ✭✭
    Answer ✓
    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.

Answers

  • Guillaume B.
    Guillaume B. ✭✭
    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

  • Guillaume B.
    Guillaume B. ✭✭
    Answer ✓
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!