Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
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
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.
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.
