excel - Need to sum a column if 2 or 3 columns contain a specific text -
i've following data set , want add values reflect "abc" in cell.
column1 column 2 column 3 column 4 column 5 abc cnn $150 abc nba better life n-h $40 lit mnm nice job abc $35 mn abc poor h-i $200 itl abc best ti $120 sql abc poor life n-t $40 lt nm great $800 abc bef the sum should return $150+$35+200+120+$400 = $905 because somewhere in cells has text "abc". tried using sumif(find) formula gives me value error. thoughts?
short answer
use array formula:
=sumproduct(if(if(len(substitute(a:a,"abc",""))<len(a:a),1,0)+if(len(substitute(b:b,"abc",""))<len(b:b),1,0)+if(len(substitute(d:d,"abc",""))<len(d:d),1,0)+if(len(substitute(e:e,"abc",""))<len(e:e),1,0)>0,1,0),c:c) note: array formulas entered ctrl + shift + enter
explaination
to test whether or not cell contains abc can use substitute forumla combined len test difference between string lengths:
len(substitute(a:a,"abc",""))<len(a:a)
we can wrap in if statement nice array of 1's , 0's
if(if(len(substitute(a:a,"abc",""))<len(a:a),1,0)
if mapped out data this:
if(if(len(substitute(a:a,"abc",""))<len(a:a),1,0) = {0, 1, 0, 0, 0, 0, 0, 0} if(if(len(substitute(b:b,"abc",""))<len(b:b),1,0) = {0, 0, 0, 1, 0, 0, 0, 0} if(if(len(substitute(d:d,"abc",""))<len(d:d),1,0) = {0, 1, 0, 0, 0, 0, 0, 1} if(if(len(substitute(e:e,"abc",""))<len(e:e),1,0) = {0, 0, 0, 1, 1, 1, 0, 0} += {0, 2, 0, 2, 1, 1, 0, 1} all have check if number in array >0 , multiply column c using sumproduct:
{0, 2, 0, 2, 1, 1, 0, 1 } >0 {0, 1, 0, 1, 1, 1, 0, 1 } *c:c {0, 150, 40, 35, 200, 120, 40, 800} = {0, 150, 0, 35, 200, 120, 0, 800} ----------------------------------------- sum = 1305
Comments
Post a Comment