title: Excel SUMIF with exact word but not case sensitive match
body: <p>I have a simplified table for this problem example</p>
  
  <pre><code>Column A       |  Column B  |  Column C
  
  war            | 1          | war
  
  War            | 2
  
  warred         | 3 
  
  war and peace  | 4
  
  awful war      | 5
  
  dead war horse | 6
  </code></pre>
  
  <p>Now I need to find all rows containing the word "war" that is not case sensitive, but must be a separate word, not a part of another word.</p>
  
  <p>For example</p>
  
  <pre><code>=SUMIF(A1:A6;"C1";B1:B6)
  </code></pre>
  
  <p>right now finds only values "war" and "War" and SUM is 3.</p>
  
  <p>I want it to find also values "war and peace", "awful war" and "dead war horse" since they all contain the word "war" and the SUM value should be 18.</p>
  
  <p>I can't use search term </p>
  
  <pre><code>"*war*"
  </code></pre>
  
  <p>since this also includes the value "warred" and this is a separate word and shouldn't match.</p>
  
  <p>One possibility is to create 4 different SUMIF-s with terms</p>
  
  <pre><code>war
  war_*
  *_war
  *_war_*
  
  _ is space
  </code></pre>
  
  <p>and then sum those four, but this is not that elegant.</p>
  
  <p>I thought SUMPRODUCT with EXACT would work, but this seems to work over columns, not rows and EXACT isn't suitable..it think.</p>
  
  <p><strong>Is there a way to match row based on word that is not case sensitive and then sum all the values in Column B that have a matching row?</strong></p>