Sum If Begins With

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:

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.

Explanation

In this example, the goal is to sum the amounts in the table using the “Start” and “End” values in columns F and G. For convenience, all data is in an Excel Table called data , which means we can use the structured reference data[Amount] to refer to amounts. An easy way to solve this problem is to use the SUMIFS function, as explained below.

SUMIFS function

The SUMIFS function can sum values in ranges based on multiple criteria. The basic function signature for SUMIFS looks like this:

=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)

In this case, we need to configure SUMIFS to sum values in the Amount column based on two criteria:

1. Amounts greater than zero (the value in cell F5)
2. Amounts that are less than or equal to 500 (the value in E5)

Conditions are supplied to SUMIFS as range/criteria pairs, so each condition will be composed of two arguments. To start off, we provide the sum_range to SUMIFS, which contains the values we want to sum:

=SUMIFS(sum_range

Next, add the first condition, which is that amounts need to be greater than zero (the value in cell F5):

=SUMIFS(data[Amount],data[Amount],">"&F5

Here we provide data[Amount] as criteria_range1 , and the “>"&F5 for criteria1 . Notice we need to concatenate the cell reference to the logical operators , and the operators are entered as text*, enclosed in double quotes (”"). Next, we need to add the second condition, for amounts less than or equal to 500 (the value in cell E5):

=SUMIFS(data[Amount],data[Amount],">"&F5,data[Amount],"<="&G5)

As before, we need to concatenate the operator as text to cell C5. When we enter this formula, it returns 1400, the total of all amounts greater than 0 and less than or equal to 500. As the formula is copied down, it returns a new total in each row, based on the values in columns F and G. The structured reference data[Amount] behaves like an absolute reference and does not change. The references to F5 and G5 are relative and change at each new row.

• Note: SUMIFS is in a group of functions that split criteria into two parts. As a result, the syntax used for operators is different from other functions. See this article for details .