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
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/106682/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":48,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
