Explanation
In this example, the goal is to calculate a sum of the values in a range that are generated with a formula. In other words, we want to sum values in a range while ignoring the values that have been entered manually. In the context of this example, the hardcoded values in C5:C12 represent actual sales values and the values in the range C13:C16 represent forecasted values. This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below.
Forecasted values
The forecasted values in the range C13:C16 are created with a formula based on the MROUND function . The formula in C13, copied down, is:
=MROUND(C12*1.05,25)
This formula is used to generate values that are 5% higher than the previous month, rounded to the nearest multiple of 25.
Sum formulas
To sum values in the range C5:C16 that are created with formulas, the formula in F6 is:
=SUMPRODUCT(ISFORMULA(sales)*sales)
This formula uses boolean logic to “filter” the numbers in sales (C5:C16) based on whether the values come from a formula or not. The ISFORMULA function created the filter like this:
ISFORMULA(sales)
ISFORMULA returns TRUE when cell contains a formula, and FALSE if not. In this case, there are 12 values in the range C5:C16, so ISFORMULA returns 12 results in an array like this:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}
Each TRUE value in this array represents a cell that contains a formula. Notice the last 4 values are TRUE. When this array is multiplied by the named range sales (C5:C16), the math operation coerces the TRUE and FALSE values to 1s and 0s. We can visualize the formula at this point like this:
=SUMPRODUCT({0;0;0;0;0;0;0;0;1;1;1;1}*sales)
After the multiplication takes place, we have a single array like this:
=SUMPRODUCT({0;0;0;0;0;0;0;0;1375;1450;1525;1600})
Now you can see how the filter works. The values not created by formulas are “zeroed out”. With just one array to process, SUMPRODUCT sums the array and returns a final result of 5950.
Not formulas
To sum values not generated by a formula, you can add the NOT function like this:
=SUMPRODUCT(NOT(ISFORMULA(sales))*sales)
This is the formula in cell F7. Here, the NOT function reverses the TRUE FALSE results returned by ISFORMULA function:
=SUMPRODUCT({1;1;1;1;1;1;1;1;0;0;0;0}*sales)
This causes the formula-created values to be zeroed out:
=SUMPRODUCT({925;1038;1105;1210;1250;1175;1230;1310;0;0;0;0})
The final result from this formula is 9243.
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.
