Return SUM only from formulas

In this article, we will learn to return SUM only from formulas in  Excel.

In simple words, while working with partially filled data. Sometimes, we need to find the sum of value having a condition. Condition is to get the sum where values are extracted with formulas only. 

For this article we will be needing the use the following functions:

  1. SUMPRODUCT function
  2. ISFORMULA function

SUMPRODUCT function is a mathematical function in Excel. It operates on multiple ranges. It multiplies the corresponding arrays and then adds them.

Total= ( A1 * B1 * C1 * .. + A2 * B2 * C2 * ..  + …)

Syntax:

= SUMPRODUCT ( array1 , [array2] , ...)

array : It is the range or a list of values.

ISFORMULA function returns TRUE where formula exists in cell_reference and returns FALSE otherwise.

Syntax:

= ISFORMULA ( reference )

reference : reference to cell, where to check formula

Now we will make a formula out of these functions. Here we will given the data and we needed sum results where formula is applied.

Use the formula:

= SUMPRODUCT ( array * ISFORMULA ( array) )

Explanation:

  • ISFORMULA function returns TRUE & FALSE on the basis of the condition being checked on cells.
  • SUMPRODUCT function considers value 1 for every TRUE value and 0 for FALSE value.
  • The product taken between corresponding values will ignore FALSE values as the values are multiplied by 0s. Only TRUE values are getting SUM as the values are multiplied by 1s.

Example:

Let's test this formula via running it on an example

Here we have a data having Price of Grains extracted by the product of Quantity to its Unit price and Some Price values are entered manually. So if I need to find the Price Sum for the value where formula extracted the Total Price.

 

Now we will use the below formula to get the SUM 

Formula:

= SUMPRODUCT ( E2 : E15 * ISFORMULA ( E2 : E15 ) )

Explanation:

  • ISFORMULA function returns TRUE & FALSE on the basis of the condition being checked on array cells from E2 to E15. 
  • SUMPRODUCT function considers value 1 for every TRUE value received and 0 for FALSE value as shown below.

=SUMPRODUCT(E2:E15*

{ TRUE ; FALSE ; FALSE ; FALSE ; TRUE ; TRUE ; FALSE ; TRUE ; FALSE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE } )

  • The product taken between corresponding values will ignore FALSE values as the values are multiplied by 0s. Only TRUE values are getting SUM as the values are multiplied by 1s as shown below.

= SUMPRODUCT ( { 58.41 ; 0 ; 0 ; 0 ; 82.84 ; 95.58 ; 0 ; 90.27 ; 0 ; 37.8 ; 78.48 ; 57.97 ; 97.72 ; 77.88 } 

Here the array to the function is given as cell_reference. Press Enter to get the result.

As you can see in the above snapshot the sum of values extracted where formula exists.

If you need to find the sum of values not having formula use the NOT function with the ISFORMULA function.

Use the formula:

= SUMPRODUCT ( E2:E15 * NOT ( ISFORMULA ( E2:E15 ) ) )

Use the formula and get the value as shown in the snapshot below.

As you can see from the above formula the you can get conditional values.  

Notes:

  1. The SUMPRODUCT function considers non - numeric values as 0s.
  2. The SUMPRODUCT function considers logic value TRUE as 1 and False as 0.
  3. The argument array must be of same length else the function returns error.

Hope this article about how to Return SUM only from formulas in Excel is explanatory. Find more articles on SUMPRODUCT functions here. Please share your query below in the comment box. We will assist you.

Related Articles

How to use the SUMPRODUCT function in Excel

How to Remove Text in Excel Starting From a Position

Validation of text entries

Create drop down list in excel with colour

Remove leading and trailing spaces from text in Excel

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube