CODEWITHSUNDEEP
excelexcel-formula
Eric Johnson
Eric Johnson

Reputation: 59

Excel: SUMIFS to sum values adjacent to text strings contained within a text string

I'm hoping to sum numeric values with a multi-conditional, multi-array function that integrates wildcard searches into a reference formula.

My data is structured as in the excerpt at http://bit.ly/Xv8tHP

enter image description here

My objective for the formula in C2 is to:

The result to output to C2 in this example would be $80.95 from C24 + $38.99 from C3 = $119.94.

For the first condition, the following formula snippet works: =SUMIFS($C$3:$C$24,$A$3:$A$24,"="&A2,[...]

But I'm stumped when it comes to integrating the second condition, whether by using ISERROR(SEARCH[...])=FALSE, or INDEX, or another reference function. The 'contained by' part of the condition seems troublesome.

Does anyone have a suggestion?

Many thanks!

Upvotes: 4

Views: 5382

Answers (2)

Excellll
Excellll

Reputation: 5785

Try this:

=SUMPRODUCT($C$3:$C$24,--(A2=$A$3:$A$24),IF(ISERROR(FIND($B$3:$B$24,B2)),0,1))

You must enter this formula as an array formula by pressing Ctrl+Shift+Enter in the formula bar. The array entry is necessary to make FIND take an array as the search text argument.

Upvotes: 5

chris neilsen
chris neilsen

Reputation: 53135

Add another helper function to another column, say E3:E24 containing formula

=FIND(B3,$B$2)

(this is case sensitive. To make to non case sensitive use SEARCH rather than FIND)

Then the SUMIFS formula is

=SUMIFS($C$3:$C$24,$A$3:$A$24,A2,$E$3:$E$24,">0")

Upvotes: 0

Related Questions