Sum If Cells Contain Specific Text

Explanation

In this example, the goal is to sum the quantities in column C when the text in column B contains “hoodie”. The challenge is that the item names (“Hoodie”, “Vest”, “Hat”) are embedded in a text string that also contains size and color. This means we need to apply criteria that looks for a substring in the item text. To solve this problem, you can use either the SUMIFS function or the SUMIF function with a wildcard . If you need a case-sensitive formula, you can use the SUMPRODUCT function with the FIND function. All three approaches are explained below.

Note: this example embeds wildcards together with the search substring to keep things simple. However, you can also use wildcards with text in another cell, as explained in this more advanced example .

Wildcards

Excel functions like SUMIF and SUMIFS support the wildcard characters “?” (any one character) and “” (zero or more characters), which can be used in criteria. Wildcards allow you to create criteria to target cells that “begin with”, “end with”, “contain 3 characters” and so on. The table below shows some examples. For this problem, we want to use the “Cells that contain text in xyz” pattern, which uses two asterisks (), one before and one after the search string like this “xyz”.

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

SUMIFS solution

One way to solve this problem is with the SUMIFS function . SUMIFS can handle multiple criteria, and the generic syntax for a single condition looks like this:

=SUMIFS(sum_range,criteria_range1,criteria1)

Notice that the sum range always comes first in the SUMIFS function. In our case, the sum_range is C5:C16, criteria_range1 is B5:B16, and criteria1 is “hoodie”. Putting it all together, the formula in cell F5 of the worksheet shown is:

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

Notice the text and both wildcards (*) are enclosed in double quotes (""). The meaning of this criteria is to match the substring “hoodie” anywhere in a text string. When the formula is entered in cell F5, it returns 22, the total quantity of “hoodie” products in the data.

SUMIF solution

This problem can also be solved with the SUMIF function , where the equivalent formula is:

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

Note that sum_range is the last argument in the SUMIF function. However, the criteria itself is identical to what we used in SUMIFS above. The result returned by SUMIF is also the same: 22.

Case-sensitive option

As mentioned above, the SUMIF and SUMIFS functions are not case-sensitive. If you need a case-sensitive solution, you can use a formula based on the SUMPRODUCT function and the FIND function like this:

=SUMPRODUCT(--ISNUMBER(FIND("Hoodie",B5:B16))*C5:C16)

Inside SUMPRODUCT, the left side of the expression tests for “Hoodie” with ISNUMBER and FIND:

--ISNUMBER(FIND("Hoodie",B5:B16))

Note the “H” in hoodie is capitalized. The FIND function is always case-sensitive, and returns the position of find_text as a number when found, and a #VALUE! error when not found. We do not need to use a wildcard like (*) because FIND automatically searches for a substring. Because there are 12 values in B5:B16, FIND returns an array of 12 results like this:

{6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7;#VALUE!;6;6}

Notice that FIND returns numbers for rows 1, 9, 11, and 12 in the data. These are the rows where the substring “Hoodie” appears in the text. Next, the ISNUMBER function converts the results from FIND into TRUE and FALSE values, and the double negative (–) converts the TRUE and FALSE values to 1s and 0s. Inside SUMPRODUCT we now have:

=SUMPRODUCT({1;0;0;0;0;0;0;0;1;0;1;1}*C5:C16)

Note: technically, the double negative (–) is unnecessary in this formula, because multiplying the TRUE and FALSE values by the numeric values in C5:C16 will automatically convert TRUE and FALSE values to 1s and 0s. However, the double negative does no harm and it makes the formula a bit easier to understand, because it indicates a Boolean operation .

When the two arrays are multiplied together, the zeros in the first array work like a filter to “cancel out” the quantity for items that are not “Hoodies”. The result inside SUMPRODUCT is a single array like this:

=SUMPRODUCT({9;0;0;0;0;0;0;0;6;0;4;3})

With only one array to process, SUMPRODUCT sums the array and returns 22 as a final result. For a more detailed explanation of FIND + ISNUMBER see this article . To adapt this formula to use text in cell references, see this example .

Explanation

In this example, the goal is to sum amounts in column C when the date in column B is between two given dates. The start date is provided in cell E5, and the end date is provided in cell F5. The date range should be inclusive - both the start date and end date should be included in the final result. A good way to solve this problem is with the SUMIFS function .

Note: for SUMIFS to work correctly, the worksheet must use valid Excel dates . All dates in Excel have a numeric value underneath, and this is what allows SUMIFS to apply the logical criteria described below.

SUMIFS with a date range

The generic syntax for using SUMIFS with a date range looks like this:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

Where the arguments above have the following meaning:

• sum_range - the range that contains values to sum
• criteria_range1 - a range that contains the dates
• criteria1 - logic to target dates greater than the start date
• criteria_range2 - a range that contains the dates
• criteria2 - logic to target dates less than the end date

In the worksheet shown, we already have a start date entered in cell E5 (15-Sep-2022) and an end date in F5 (15-Oct-2022), so we will need to use those cell references as we enter criteria into SUMIFS. We start off with the sum range, which contains the amounts in C5:C16:

=SUMIFS(C5:C16,

Note that values in this range must be numeric . Next, we need to add the logic needed to target dates greater than or equal to the date in cell E5. We do this by entering two arguments: criteria_range1 and criteria1 . We first add the range that contains the dates (B5:B16), then we add the criteria, which we enter as “>="&E5:

=SUMIFS(C5:C16,B5:B16,">="&E5,

This tricky syntax is a quirk of all the RACON functions in Excel . Notice that we need to enclose the operator in double quotes (">=”), and concatenate the cell reference E5 with an ampersand (&). ​​​​​​If we enter this formula as-is, we get a sum of all amounts in C5:C16 that are greater than or equal to 15-Sep-2022, which is $32,050:

=SUMIFS(C5:C16,B5:B16,">="&E5) // returns 32050

This is a valid result, but we aren’t done yet, because we still need to add the second criteria for the end date in cell F5. We do this in the same way, by adding two more arguments: criteria_range2 and criteria2 . We first add the date range (B5:B16):

=SUMIFS(C5:C16,B5:B16,">="&E5,B5:B16,

Then we add the criteria:

=SUMIFS(C5:C16,B5:B16,">="&E5,B5:B16,"<="&F5)

Notice we use the same date range (B5:B16) for criteria_range2 and, as before, we need to enclose the operator in double quotes ("<=") and concatenate cell F5 with an ampersand (&). When we enter the formula, SUMIFS returns $13,500, the total of Amounts in C5:C16 that are between 15-Sep-2022 and 15-Oct-2022, inclusive. If the dates in cell E5 or cell F5 are changed, the formula will immediately calculate a new result.

With hard-coded dates

In general, it is a best practice to enter the dates you want to use in a formula directly on the worksheet. This makes the worksheet more “transparent” because you can easily see the dates being used, and easily change the dates when needed. It also reduces the problem of Excel incorrectly interpreting a date, since you can see at a glance if dates on the worksheet are displayed correctly. That said, there may be times when you need to hardcode dates directly into a formula. The safest way to do this is to use the DATE function , which creates a valid date with separate year , month , and day arguments like this:

=DATE(year,month,day)

In this example, we can adapt the SUMIFS function above to use hardcoded dates by incorporating the DATE function inside the SUMIFS function like this:

=SUMIFS(C5:C16,B5:B16,">="&DATE(2022,9,15),B5:B16,"<="&DATE(2022,10,15))

• sum_range - C5:C16
• criteria_range1 - B5:B16
• criteria1 - “>="&DATE(2022,9,15)
• criteria_range2 - B5:B16
• criteria1 - “<="&DATE(2022,10,15)

Notice we still need to concatenate the logical operators to the DATE function using an ampersand (&).