Sum if ends with

To sum numbers when corresponding text values end with specific text, you can use the SUMIFS function or the SUMIF function. In the example shown, the formula in F5 is:

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

The result is 19, which is the sum of the quantities in the range C5:C16 when the corresponding item names in B5:B16 end with "small". Note the SUMIFS function is not case-sensitive.

Explanation

In this example, the goal is to sum the Quantity in C5:C16 when the Item in B5:B16 ends with "small". To solve this problem, you can use either the SUMIFS function or the SUMIF function with the asterisk (*) wildcard, as explained below.

Wildcards

Certain Excel functions like SUMIFS and SUMIF 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.

SUMIFS solution

The generic syntax for the SUMIFS function with a single condition looks like this:

=SUMIFS(sum_range,range1,criteria1)

Notice that the sum range always comes first in the SUMIFS function. To use SUMIFS to sum the Quantity in C5:C16 when the Item in B5:B16 ends with "small", the formula in F5 looks like this:

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

The criteria "*small" means match text that ends with "small". Notice both the text and the wildcard must be enclosed in double quotes (""). Also note that SUMIFS is not case-sensitive. The criteria "*small" will match "Small", "SMALL", or "small".

SUMIF function

You can also solve this problem with the SUMIF function, an older function in Excel that supports just one condition. The generic syntax for the SUMIF function looks like this:

=SUMIF(range,criteria,sum_range)

Notice sum_range comes last in the SUMIF function. To sum the Quantity in C5:C16 when the Item in B5:B16 ends with "small", the equivalent SUMIF formula is: