Sum if begins with

To sum numbers if values in a criteria range begin with specific text, you can use the SUMIF function or the SUMIFS function. In the example shown, the formula in F5 is:

=SUMIF(B5:B16,"sha*",C5:C16)

The result is $30.45, the sum of Shampoo ($9.50), Shaving Cream (11.95), and Shaving Soap ($9.00). Note the SUMIF function is not case-sensitive.

Explanation

In this example, the goal is to sum the Price in column C when the Product in column B begins with "sha". To solve this problem, you can use either the SUMIF function or the SUMIFS function with the asterisk (*) wildcard, as explained below.

Wildcards

Certain Excel functions like SUMIF and SUMIFS support the wildcard characters "?" (any one character) and "*" (zero or more characters), which can be used in criteria. These wildcards allow you to create criteria such as "begins with", "ends with", "contains 3 characters", etc. as shown in the table below:

Target	Criteria
Cells with 3 characters	"???"
Cells like "bed", "bad", "bid", etc	"b?d"
Cells that begin with "xyz"	"xyz*"
Cells that end with "xyz"	"*xyz"

Note that wildcards are enclosed in double quotes ("") when they appear in criteria.

SUMIF solution

The generic syntax for the SUMIF function looks like this:

=SUMIF(range,criteria,sum_range)

In this example, the formula to sum Price when Product begins with "sha" is:

=SUMIF(B5:B16,"sha*",C5:C16)

The criteria "sha*" means cells that begin with "sha". Notice you must enclose the text and the wildcard in double quotes (""). Also note that SUMIF is not case-sensitive. The criteria "sha*" will match "Shampoo", or "SHAMPOO".

SUMIFS solution

You can also use the SUMIFS function to sum if cells begin with. SUMIFS can handle multiple criteria, and the order of the arguments is different from SUMIF. The generic syntax for a single condition looks like this:

=SUMIFS(sum_range,range1,criteria1)

Notice that the sum range always comes first in the SUMIFS function. The equivalent SUMIFS formula for this example is:

=SUMIFS(C5:C16,B5:B16,"sha*")

The criteria used with SUMIFS is the same as that used in SUMIF, with the text and wildcard are both enclosed in double quotes (""). Like SUMIF, the SUMIFS function is not case-sensitive.