Excel sumif or sumifs with concatenated values

Question

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.

Here is what is expect :

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" & "(failed)"

Sumif

=SUMIF(A:A,E3&"*",B:B)

Sumifs

=SUMIFS(B:B,A:A,E3,A:A,E3 & "*")

when i tried to use the concatenation in sumifs it returns 0

=SUMIFS(B:B,A:A,C60,A:A,TRIM(C60)&"(failed)")

Your inputs much appreciate.

Thank you!

Answer 1

Try SUMPRODUCT:

=SUMPRODUCT(((E2=$A$2:$A$10)+(E2&"(failed)"=$A$2:$A$10))*$B$2:$B$10)

Edit:

Formula can be a little shortened:

=SUMPRODUCT(((E2&{"";"(failed)"}=$A$2:$A$10))*$B$2:$B$10)