CODEWITHSUNDEEP
excelexcel-formula
Simon_Cel
Simon_Cel

Reputation: 1

Excel - Sumif cell contains text across columns

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!

Upvotes: 0

Views: 1332

Answers (2)

I like Excel very much
I like Excel very much

Reputation: 748

Late to the party, I put the source data in A1:D3 and the text to be searched in B8.

=SUMPRODUCT(--(NOT(ISERROR(SEARCH(B8&" ",A1:C3))))*D1:D3)

Finds the cells that contain the word in B8 (with a space after it) and multiplies by the column with the numbers. This works for the data given.

But, for a wider ranging application I would include punctuation, at least "," and ".". Here's how you do that:

=SUMPRODUCT(--(NOT(ISERROR(SEARCH(B8&" ",A1:C3)))+NOT(ISERROR(SEARCH(B8&",",A1:C3)))+NOT(ISERROR(SEARCH(B8&".",A1:C3))))*D1:D3)

Each new type of punctuation is added with this string:

+NOT(ISERROR(SEARCH(B8&"!",A1:C3)))

With the punctuation you want to add replacing the exclamation point.

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

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.

Upvotes: 0

Related Questions