title: Excel sumif or sumifs with concatenated values
body: <p>I have problem in collating the results from table. Following is the data i have . Here i need to combine the count for test names and test names(failed). i.e., I need to take and collate Blue and Blue(Failed). However this shouldn't include "Blue ocean" entities.</p>
  
  <p><a href="https://i.stack.imgur.com/q0jJL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q0jJL.png" alt="original data "></a></p>
  
  <p>Here is what is expect :</p>
  
  <p><a href="https://i.stack.imgur.com/Ncgwv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ncgwv.png" alt="expected"></a></p>
  
  <p>I tried using sumif, sumifs and vlookup. However i couldn't achieve what i wanted . Is there a ways to have concatenation in the criteria value of this function like "cell value containing blue" &amp; "(failed)"</p>
  
  <p>Sumif </p>
  
  <pre><code>=SUMIF(A:A,E3&amp;"*",B:B)
  </code></pre>
  
  <p>Sumifs </p>
  
  <pre><code>=SUMIFS(B:B,A:A,E3,A:A,E3 &amp; "*")
  </code></pre>
  
  <p>when i tried to use the concatenation in sumifs it returns 0</p>
  
  <pre><code>=SUMIFS(B:B,A:A,C60,A:A,TRIM(C60)&amp;"(failed)")
  </code></pre>
  
  <p>Your inputs much appreciate.</p>
  
  <p>Thank you!</p>