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

edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭

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

This discussion has been closed.