Excel - Sumif cell contains text across columns

Question

I'd like some help with a forumla to sum a column if the cells across 3 columns contain a set bit of text. I've played around with sumproduct, sumif/sumifs etc and can't get it working.

Here is an example. I would like a formula that if I wanted to sum up the amount against rows that contain blue I'd get 30 as the result, or if I wanted to sum the rows with red in I'd get 20 -

|Text 1                     | Text 2                         | Text 3                      | Amount|
|---------------------------|--------------------------------|-----------------------------|-------|
|blue widgets               | and green widgets              | oh, and red widgets         | 10    |
|redish widgets             | kinda orangey widgets actually | no, they are red widgets    | 10    |
|purple widgets are rubbish | blue widgets are best          | get your blue widgets here  | 10    |

Hope I haven't messed up the table formatting!

Using that provided sample data, wouldn't both red and blue give a result of 20?? — tigeravatar
@tigeravatar No, because blue is listed three times, once in each column and twice in one row. — Brian

Tom Sharpe Tom Sharpe · Accepted Answer · 2016-10-28T16:33:58

You could do this more generally, but for the particular case of three columns containing text it would be:-

=SUM(IF(ISNUMBER(SEARCH(F$1,$A$2:$A$4))+ISNUMBER(SEARCH(F$1,$B$2:$B$4))+ISNUMBER(SEARCH(F$1,$C$2:$C4)),$D$2:$D$4))

which is an array formula and must be entered with CtrlShiftEnter

and yes this is assuming that redish (or even reddish) would count.

Excel - Sumif cell contains text across columns

2 Answers