Help! sum children if word1 and if word 2 are in row

BrianCarcich

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 

Atus Bartal

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

Atus Bartal

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

J. Craig Williams

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

 

JohnHinkle

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...)

 

J. Craig Williams

John, it seems so obvious when you say it like that. 

 

Craig