Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Help! sum children if word1 and if word 2 are in row
I am trying to sum children in a column if two specific words are present in a row(s).
IF "word1" ANDIF "word2" are present = sum of children
Comments
-
Brian,
=SUMIF(Word2:Word8, "=Word1", Number2:Number8)
or
=SUMIF(Word2:Word8, "=Word1", CHILDREN(Number1))
in 'Word1' cell work for me with these columns:
(in 'Number1' cell there is a '=sum(children())' formula.)
I hope it helps.
Atus
-
Ooops,
I've just noticed you write "IF "word1" ANDIF "word2" are present" -- so do you want to search for both of the words? Then we need a new column that shows if the words are present or not. The simplest - I think - is this:
=FIND("Word1", Word2) * FIND("Word2", Word2) [pointing at Word2 cell in IsThere2 cell]:
So it gives you a number different from zero if both words are found. Then we just have to set the "SUMIF" formula to this column:
=SUMIF(IsThere2:IsThere8, "<>0", Number2:Number8) [in cell 'IsThere1']
Please, let me know if it helps.
Atus
-
Brian,
The way I read your question, you are looking for word1 or word2 in more than one column in the row. Is that correct?
That gets a little more complicated that Atus' solution, but is also possible, I think.
Craig
-
If you are looking for word1 and word2 in two columns, use a SUMIFS
=SUMIFS(sum_range, criteria_range1, criteria_value1, criteria_range2, criteria_value2...)
-
John, it seems so obvious when you say it like that.
Craig
