For our first example, we want to sum all the values in the “Quantity Sold” column where the “Company ID” contains the characters “AT” anywhere in the text; beginning, middle, or end.
Select cell G6 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, F6 )
Observe that this fails to return any “hits” because it is looking for “Company ID’s” that exactly match the letters “AT”.
We want the formula to be more forgiving.
Update the formula in cell G6 with the following modification:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, ”*” & F6 & ”*” )
By placing an asterisk both in front and following the text in cell F6, we have told the SumIFs that the characters “AT” can occur anywhere within the “Company ID”. Because we are searching for text, we must enclose the asterisks (*) in double-quotes and concatenate the components with ampersands (&).
Fill the formula from cell G6 down to cell G9.