Sometimes values are created by formulas in cells. If we want to sum values which are created by formulas from a range, but some values which are hardcoded also list in the same range, how can we filter out matched data and only sum the filtered values correctly? By the way, if we want to sum cells that contains formulas, how can we do? Actually, we can apply **ISFORMULA** function to help us to solve our problem, it can return true of false if call contains a formula or not.

In this article, we will show you the method of “**SUM for Cells that Contains Formula**” with the application of **ISFORMULA** **and SUMPRODUCT **functions. We will create a simple formula which consists of the two functions.

We will provide a very simple instance in this article, and we will show you the syntax, argument and the usage of **ISFORMULA and SUMPRODUCT **functions. We will let you know how the formula works step by step clearly. After reading the article, you may know that in which situations we can choose **SUMPRODUCT** function to sum data.

Table of Contents

**EXAMPLE**

In above table, we list the sum of two numbers in SUM column. But for some other numbers in the same column, they are hardcoded for example 5 in C4 and 10 in C7. In E column, we want to sum numbers which are created by formula. If we want to sum numbers ignore the hardcoded numbers, we need the help of **ISFORMULA **function, it can distinguish if number is generated by a formula or not. Then after filtering numbers, we can apply **SUMPRODUCT** function to sum filtered numbers.

**FORMULA – APPLY SUMPRODUCT FUNCTION**

**Step 1: **In E2, enter the formula

=SUMPRODUCT(C2:C9*ISFORMULA(C2:C9))

**Step 2: **Press **Enter** after typing the formula.

We can see that the result is 38, it equals to 9+4+8+5+6+6=38. The formula works correctly.

**FUNCTION INTRODUCTION**

**SUMPRODUCT** function can be seen as **SUM+PRODUCT**.

For **SUMPRODUCT** function, the syntax is:

=SUMPRODUCT(array1,array2,array3, ...)

For example, enter **=SUMPRODUCT({1,2},{2,3})** in any cell, the we can get value** 8**, it equals to** 1*2+2*3=8**. You can also enter **=SUMPRODUCT({1,2}*{2,3})**, you can get the same result. The argument array can be a real array **{1,2,3}** for example or a range reference like** C2:C7**.

If there is only array in the formula, **SUMPRODUCT** will sum the numbers in the array.

Besides,** SUMPRODUCT** function allows entering texts, logical operators and expressions like (product=cap), texts should be enclosed into ().

**ISFORMULA** function can return **True** if cell contains a formula, or if a number is created by a formula; if returns **False**, that means this cell doesn’t contain any formula.

For **ISFORMULA** function, the syntax is:

=ISFORMULA (reference)

**ARGUMENTS EXPLAINATION**

In this formula **=SUMPRODUCT(C2:C9*ISFORMULA(C2:C9)), **we applied two functions.

For **ISFORMULA** function, reference is **C2:C9**.

For **SUMPRODUCT **function, there is only one array argument, it is **C2:C9*ISFORMULA(C2:C9).**

**HOW THIS FORMULA WORKS**

After explaining argument in the formula, let’s start to execute this formula from inside to outside.

First, in the formula bar, we execute **ISFORMULA** part. Select **ISFORMULA(C2:C9)**, press **F9** to get result, we found that an array is returned. **True** and **False** are saved in this array.

Then select **C2:C9** in the formula bar, press **F9**. We can see that values in C2:C9 are expanded in this array.

Actually, in excel, if the two arrays are multiplied by each other, **TRUE** will be forced to convert to 1 and **FALSE** will be converted to 0 in math operation. So, **{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}** will be converted to **{1,1,0,1,1,0,1,1}** in next step of calculation. At last, this formula can be seen as

=SUMPRODUCT({9;4;5;8;5;10;6;6}*{1,1,0,1,1,0,1,1}).

As** SUMPRODUCT **function can sum products from arrays. That’s why in this case we apply **SUMPRODUCT** function instead of other sum related functions.

Continue executing the formula. Select **{9;4;5;8;5;10;6;6}*{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}** in the formula bar, press **F9**. Let’s see if products from above two arrays are displayed.

As we can see, products are saved a new array **{9;4;0;8;5;0;6;6}**.

Select **=SUMPRODUCT({9;4;0;8;5;0;6;6})** in the formula bar, press **F9, **38 is displayed in formula bar. 38 is the final result.

**COMMENTS**

**1**. In this instance, if we want to sum cells which doesn’t contain any cell, we can add **NOT** before **ISFORMULA**

Enter the follow formula in Cell E2:

=SUMPRODUCT(C2:C9*NOT(ISFORMULA(C2:C9)))

We can get result 15.

**NOT function here can return the opposite of the given formula ISFORMULA(C2:C9).**

2. In this case we can also use **SUM** function to replace **SUMPRODUCT** function. But **SUMPRODUCT** function can handle arrays directly without entering **Ctrl + Shift + Enter**, so we often apply **SUMPRODUCT** function in similar situations.

After applying **SUM** function, press **Ctrl + Shift + Enter **simultaneously, we can also get correct result 38.

** **

**SUMMARY**

**1**.** SUMPRODUCT** function can handle multiple arrays, and it sum up the products.

** 2. SUMPRODUCT **function allows user defined range, wildcards, logical operators, expressions.

**3. ISFORMULA** function can return **True/False** if cell contains a formula or not.

**4. NOT **function can return the opposite of a given formula or logical operation in excel.

## Related Functions

- Excel SUMPRODUCT function

The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:**=**SUMPRODUCT (array1,[array2],…)… - Excel SUM function

The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…) - Excel NOT function

The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…