SUMIF Formula with exact match text

It would help if you attached a sample Excel workbook, and you can do this even if you have a low post-count (you can't post links if you have less than 5 posts).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do NOT try to use the Paperclip icon, as this does not work on this forum.

Hope this helps.

Pete

Maybe like this:

=SUMPRODUCT(ISNUMBER(SEARCH( " "&$A2&" "," "&Sheet1!$A2:$A1000&" "))* Sheet1!$B$2:$B$1000)

Or

Formula:

Please Login or Register  to view this content.

Sure, it's taking 3 variations of the criteria, the first looks for the content of A2 as the first word in the cells of the sumif criteria range, the second looks for it as the last word, the third looks at everything else in the middle. The spaces between A2 and the wildcard character ensure that the criteria word is matched as a whole, not to a longer word as you found with your first formula.

$A2&" *" first word
"* "&$A2 last word
"* "&$A2&" *" middle word(s)

CHOOSE({1,2,3} creates an array to allow 3 criteria to be entered into a single argument of sumif.

SUMPRODUCT enforces the array and adds the 3 individual results together.

Thank you very much, it's really helpful and useful