Articles

3.4: Sum-to-Product and Product-to-Sum Formulas


Learning Objectives

  • Express products as sums.
  • Express sums as products.

A band marches down the field creating an amazing sound that bolsters the crowd. That sound travels as a wave that can be interpreted using trigonometric functions.

For example, Figure (PageIndex{2}) represents a sound wave for the musical note A. In this section, we will investigate trigonometric identities that are the foundation of everyday phenomena such as sound waves.

Expressing Products as Sums

We have already learned a number of formulas useful for expanding or simplifying trigonometric expressions, but sometimes we may need to express the product of cosine and sine as a sum. We can use the product-to-sum formulas, which express products of trigonometric functions as sums. Let’s investigate the cosine identity first and then the sine identity.

Expressing Products as Sums for Cosine

We can derive the product-to-sum formula from the sum and difference identities for cosine. If we add the two equations, we get:

[egin{align*} cos alpha cos eta+sin alpha sin eta&= cos(alpha-eta)[4pt] underline{+ cos alpha cos eta-sin alpha sin eta}&= underline{ cos(alpha+eta) }[4pt] 2 cos alpha cos eta&= cos(alpha-eta)+cos(alpha+eta)end{align*}]

Then, we divide by 2 to isolate the product of cosines:

[ cos alpha cos eta= dfrac{1}{2}[cos(alpha-eta)+cos(alpha+eta)] label{eq1}]

How to: Given a product of cosines, express as a sum

  1. Write the formula for the product of cosines.
  2. Substitute the given angles into the formula.
  3. Simplify.

Example (PageIndex{1}): Writing the Product as a Sum Using the Product-to-Sum Formula for Cosine

Write the following product of cosines as a sum: (2cosleft(dfrac{7x}{2} ight) cosleft(dfrac{3x}{2} ight)).

Solution

We begin by writing the formula for the product of cosines (Equation ef{eq1}):

[ cos alpha cos eta = dfrac{1}{2}[ cos(alpha-eta)+cos(alpha+eta) ] onumber ]

We can then substitute the given angles into the formula and simplify.

[egin{align*} 2 cosleft(dfrac{7x}{2} ight)cosleft(dfrac{3x}{2} ight)&= 2left(dfrac{1}{2} ight)[ cosleft(dfrac{7x}{2}-dfrac{3x}{2} ight)+cosleft(dfrac{7x}{2}+dfrac{3x}{2} ight) ][4pt] &= cosleft(dfrac{4x}{2} ight)+cosleft(dfrac{10x}{2} ight) [4pt] &= cos 2x+cos 5x end{align*}]

Exercise (PageIndex{1})

Use the product-to-sum formula (Equation ef{eq1}) to write the product as a sum or difference: (cos(2 heta)cos(4 heta)).

Answer

(dfrac{1}{2}(cos 6 heta+cos 2 heta))

Expressing the Product of Sine and Cosine as a Sum

Next, we will derive the product-to-sum formula for sine and cosine from the sum and difference formulas for sine. If we add the sum and difference identities, we get:

[egin{align*} cos alpha cos eta+sin alpha sin eta&= cos(alpha-eta)[4pt] underline{+ cos alpha cos eta-sin alpha sin eta}&= cos(alpha+eta)[4pt] 2 cos alpha cos eta&= cos(alpha-eta)+cos(alpha+eta)[4pt] ext{Then, we divide by 2 to isolate the product of cosines:}[4pt] cos alpha cos eta&= dfrac{1}{2}left[cos(alpha-eta)+cos(alpha+eta) ight] end{align*}]

Example (PageIndex{2}): Writing the Product as a Sum Containing only Sine or Cosine

Express the following product as a sum containing only sine or cosine and no products: (sin(4 heta)cos(2 heta)).

Solution

Write the formula for the product of sine and cosine. Then substitute the given values into the formula and simplify.

[egin{align*} sin alpha cos eta&= dfrac{1}{2}[ sin(alpha+eta)+sin(alpha-eta) ][4pt] sin(4 heta)cos(2 heta)&= dfrac{1}{2}[sin(4 heta+2 heta)+sin(4 heta-2 heta)][4pt] &= dfrac{1}{2}[sin(6 heta)+sin(2 heta)] end{align*}]

Exercise (PageIndex{2})

Use the product-to-sum formula to write the product as a sum: (sin(x+y)cos(x−y)).

Answer

(dfrac{1}{2}(sin 2x+sin 2y))

Expressing Products of Sines in Terms of Cosine

Expressing the product of sines in terms of cosine is also derived from the sum and difference identities for cosine. In this case, we will first subtract the two cosine formulas:

[egin{align*} cos(alpha-eta)&= cos alpha cos eta+sin alpha sin eta[4pt] underline{-cos(alpha+eta)}&= -(cos alpha cos eta-sin alpha sin eta)[4pt] cos(alpha-eta)-cos(alpha+eta)&= 2 sin alpha sin eta[4pt] ext{Then, we divide by 2 to isolate the product of sines:}[4pt] sin alpha sin eta&= dfrac{1}{2}[ cos(alpha-eta)-cos(alpha+eta) ] end{align*}]

Similarly we could express the product of cosines in terms of sine or derive other product-to-sum formulas.

THE PRODUCT-TO-SUM FORMULAS

The product-to-sum formulas are as follows:

[cos alpha cos eta=dfrac{1}{2}[cos(alpha−eta)+cos(alpha+eta)]]

[sin alpha cos eta=dfrac{1}{2}[sin(alpha+eta)+sin(alpha−eta)]]

[sin alpha sin eta=dfrac{1}{2}[cos(alpha−eta)−cos(alpha+eta)]]

[cos alpha sin eta=dfrac{1}{2}[sin(alpha+eta)−sin(alpha−eta)]]

Example (PageIndex{3}): Express the Product as a Sum or Difference

Write (cos(3 heta) cos(5 heta)) as a sum or difference.

Solution

We have the product of cosines, so we begin by writing the related formula. Then we substitute the given angles and simplify.

[egin{align*} cos alpha cos eta&= dfrac{1}{2}[cos(alpha-eta)+cos(alpha+eta)][4pt] cos(3 heta)cos(5 heta)&= dfrac{1}{2}[cos(3 heta-5 heta)+cos(3 heta+5 heta)][4pt] &= dfrac{1}{2}[cos(2 heta)+cos(8 heta)]qquad ext{Use even-odd identity} end{align*}]

Exercise (PageIndex{3})

Use the product-to-sum formula to evaluate (cos dfrac{11pi}{12} cos dfrac{pi}{12}).

Answer

(dfrac{−2−sqrt{3}}{4})

Expressing Sums as Products

Some problems require the reverse of the process we just used. The sum-to-product formulas allow us to express sums of sine or cosine as products. These formulas can be derived from the product-to-sum identities. For example, with a few substitutions, we can derive the sum-to-product identity for sine. Let (dfrac{u+v}{2}=alpha) and (dfrac{u−v}{2}=eta).

Then,

[egin{align*} alpha+eta&= dfrac{u+v}{2}+dfrac{u-v}{2}[4pt] &= dfrac{2u}{2}[4pt] &= u end{align*}]

[egin{align*} alpha-eta&= dfrac{u+v}{2}-dfrac{u-v}{2}[4pt] &= dfrac{2v}{2}[4pt] &= v end{align*}]

Thus, replacing (alpha) and (eta) in the product-to-sum formula with the substitute expressions, we have

[egin{align*} sin alpha cos eta&= dfrac{1}{2}[sin(alpha+eta)+sin(alpha-eta)][4pt] sin left ( frac{u+v}{2} ight ) cos left ( frac{u-v}{2} ight )&= frac{1}{2}[sin u + sin v]qquad ext{Substitute for } (alpha+eta) ext{ and } (alphaeta)[4pt] 2sinleft(dfrac{u+v}{2} ight) cosleft(dfrac{u-v}{2} ight)&= sin u+sin v end{align*}]

The other sum-to-product identities are derived similarly.

SUM-TO-PRODUCT FORMULAS

The sum-to-product formulas are as follows:

[sin alpha+sin eta=2sinleft(dfrac{alpha+eta}{2} ight)cosleft(dfrac{alpha−eta}{2} ight)]

[sin alpha-sin eta=2sinleft(dfrac{alpha-eta}{2} ight)cosleft(dfrac{alpha+eta}{2} ight)]

[cos alpha−cos eta=−2sinleft(dfrac{alpha+eta}{2} ight)sinleft(dfrac{alpha−eta}{2} ight)]

[cos alpha+cos eta=2sinleft(dfrac{alpha+eta}{2} ight)sinleft(dfrac{alpha−eta}{2} ight)]

Example (PageIndex{4}): Writing the Difference of Sines as a Product

Write the following difference of sines expression as a product: (sin(4 heta)−sin(2 heta)).

Solution

We begin by writing the formula for the difference of sines.

[egin{align*} sin alpha-sin eta&= 2sinleft(dfrac{alpha-eta}{2} ight)cosleft(dfrac{alpha+eta}{2} ight)[4pt] ext {Substitute the values into the formula, and simplify.}[4pt] sin(4 heta)-sin(2 heta)&= 2sinleft(dfrac{4 heta-2 heta}{2} ight) cosleft(dfrac{4 heta+2 heta}{2} ight)[4pt] &= 2sinleft(dfrac{2 heta}{2} ight) cosleft(dfrac{6 heta}{2} ight)[4pt] &= 2 sin heta cos(3 heta) end{align*}]

Exercise (PageIndex{4})

Use the sum-to-product formula to write the sum as a product: (sin(3 heta)+sin( heta)).

Answer

(2sin(2 heta)cos( heta))

Example (PageIndex{5}): Evaluating Using the Sum-to-Product Formula

Evaluate (cos(15°)−cos(75°)). Check the answer with a graphing calculator.

Solution

We begin by writing the formula for the difference of cosines.

[egin{align*}
cos alpha-cos eta&= -2 sinleft(dfrac{alpha+eta}{2} ight) sinleft(dfrac{alpha-eta}{2} ight)[4pt]
ext {Then we substitute the given angles and simplify.}[4pt]
cos(15^{circ})-cos(75^{circ})&= -2sinleft(dfrac{15^{circ}+75^{circ}}{2} ight) sinleft(dfrac{15^{circ}-75^{circ}}{2} ight)[4pt]
&= -2sin(45^{circ}) sin(-30^{circ})[4pt]
&= -2left(dfrac{sqrt{2}}{2} ight)left(-dfrac{1}{2} ight)[4pt]
&= dfrac{sqrt{2}}{2}
end{align*}]

Example (PageIndex{6}): Proving an Identity

Prove the identity:

[dfrac{cos(4t)−cos(2t)}{sin(4t)+sin(2t)}=− an t]

Solution

We will start with the left side, the more complicated side of the equation, and rewrite the expression until it matches the right side.

[egin{align*} dfrac{cos(4t)-cos(2t)}{sin(4t)+sin(2t)}&= dfrac{-2 sinleft(dfrac{4t+2t}{2} ight) sinleft(dfrac{4t-2t}{2} ight)}{2 sinleft(dfrac{4t+2t}{2} ight) cosleft(dfrac{4t-2t}{2} ight)}[4pt] &= dfrac{-2 sin(3t)sin t}{2 sin(3t)cos t}[4pt] &= -dfrac{sin t}{cos t}[4pt] &= - an t end{align*}]

Analysis

Recall that verifying trigonometric identities has its own set of rules. The procedures for solving an equation are not the same as the procedures for verifying an identity. When we prove an identity, we pick one side to work on and make substitutions until that side is transformed into the other side.

Example (PageIndex{7}): Verifying the Identity Using Double-Angle Formulas and Reciprocal Identities

Verify the identity ({csc}^2 heta−2=cos(2 heta)sin2 heta).

Solution

For verifying this equation, we are bringing together several of the identities. We will use the double-angle formula and the reciprocal identities. We will work with the right side of the equation and rewrite it until it matches the left side.

[egin{align*} cos(2 heta)sin2 heta&= dfrac{1-2 {sin}^2 heta}{{sin}^2 heta}[4pt] &= dfrac{1}{{sin}^2 heta}-dfrac{2 {sin}^2 heta}{{sin}^2 heta}[4pt] &= {csc}^2 heta - 2 end{align*}]

Exercise (PageIndex{5})

Verify the identity ( an heta cot heta−{cos}^2 heta={sin}^2 heta).

Answer

[egin{align*} an heta cot heta-{cos}^2 heta&= left(dfrac{sin heta}{cos heta} ight)left(dfrac{cos heta}{sin heta} ight)-{cos}^2 heta[4pt] &= 1-{cos}^2 heta[4pt] &= {sin}^2 heta end{align*}]

Media

Access these online resources for additional instruction and practice with the product-to-sum and sum-to-product identities.

  • Sum to Product Identities
  • Sum to Product and Product to Sum Identities

Key Equations

Product-to-sum Formulas

[cos alpha cos eta=dfrac{1}{2}[cos(alpha−eta)+cos(alpha+eta)] onumber ]

[sin alpha cos eta=dfrac{1}{2}[sin(alpha+eta)+sin(alpha−eta)] onumber ]

[sin alpha sin eta=dfrac{1}{2}[cos(alpha−eta)−cos(alpha+eta)] onumber ]

[cos alpha sin eta=dfrac{1}{2}[sin(alpha+eta)−sin(alpha−eta)] onumber ]

Sum-to-product Formulas

[sin alpha+sin eta=2sin(dfrac{alpha+eta}{2})cos(dfrac{alpha−eta}{2}) onumber ]

[sin alpha-sin eta=2sin(dfrac{alpha-eta}{2})cos(dfrac{alpha+eta}{2}) onumber ]

[cos alpha−cos eta=−2sin(dfrac{alpha+eta}{2})sin(dfrac{alpha−eta}{2}) onumber ]

[cos alpha+cos eta=2sin(dfrac{alpha+eta}{2})sin(dfrac{alpha−eta}{2}) onumber ]

Key Concepts

  • From the sum and difference identities, we can derive the product-to-sum formulas and the sum-to-product formulas for sine and cosine.
  • We can use the product-to-sum formulas to rewrite products of sines, products of cosines, and products of sine and cosine as sums or differences of sines and cosines. See Example (PageIndex{1}), Example (PageIndex{2}), and Example (PageIndex{3}).
  • We can also derive the sum-to-product identities from the product-to-sum identities using substitution.
  • We can use the sum-to-product formulas to rewrite sum or difference of sines, cosines, or products sine and cosine as products of sines and cosines. See Example (PageIndex{4}).
  • Trigonometric expressions are often simpler to evaluate using the formulas. See Example (PageIndex{5}).
  • The identities can be verified using other formulas or by converting the expressions to sines and cosines. To verify an identity, we choose the more complicated side of the equals sign and rewrite it until it is transformed into the other side. See Example (PageIndex{6}) and Example (PageIndex{7}).

Portal

If you have permission to edit this content, using the "Reuse / Edit" action will allow you to check the content out into your Personal Workspace or a shared Workgroup and then make your edits.

Derive a copy

If you don't have permission to edit the content, you can still use "Reuse / Edit" to adapt the content by creating a derived copy of it and then editing and publishing the copy.

Add collection to:

Add module to:

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags?

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

Add collection to:

Add module to:

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.


3.5.1: Sum to Product Formulas for Sine and Cosine

Relation of the sum or difference of two trigonometric functions to a product.

Can you solve problems that involve the sum of sines or cosines? For example, consider the equation:

You could just compute each expression separately and add their values at the end. However, there is an easier way to do this. You can simplify the equation first, and then solve.

Sine and Cosine Sum to Product Formulas

In some problems, the product of two trigonometric functions is more conveniently found by the sum of two trigonometric functions by use of identities.

This can be verified by using the sum and difference formulas:

The following variations can be derived similarly:

Here are some problems using this type of transformation from a sum of terms to a product of terms.

1. Change (sin 5x&minussin 9x) into a product.

Use the formula (sin alpha &minussin eta =2sin dfrac<2> imes cos dfrac<2>).

2. Change (cos (&minus3x)+cos 8x) into a product.

Use the formula (cos alpha +cos eta =2cos dfrac <2> imes cos dfrac<2>)

3. Change (2sin 7xcos 4x) to a sum.

This is the reverse of what was done in the previous two examples. Looking at the four formulas above, take the one that has sine and cosine as a product, (sin alpha +sin eta =2sin dfrac <2> imes cos dfrac<2>). Therefore, (7x=dfrac<2>) and (4x=dfrac<2>).

So, this translates to (sin (11x)+sin (3x)). A shortcut for this problem, would be to notice that the sum of (7x) and (4x) is (11x) and the difference is (3x).

Earlier, you were asked to solve

You can easily transform this equation into a product of two trig functions using:

Substituting the known quantities:

(cos 10t+cos 3t=2cos dfrac<13t> <2> imes cos dfrac<7t><2>=2cos (6.5t)cos (3.5t))

Express the sum as a product: (sin 9x+sin 5x)

Using the sum-to-product formula:

(egin sin 9x+sin 5x &2left(sin left(dfrac<9x+5x><2> ight)cos left(dfrac<9x&minus5x><2> ight) ight) & 2sin 7xcos 2x end)


Sum-to-Product Formulas

Sum-to-Product Formulas
  • (sin u + sin v = 2sinleft(frac<2> ight)cosleft(frac<2> ight))
  • (sin u - sin v = 2cosleft(frac<2> ight)sinleft(frac<2> ight))
  • (cos u + cos v = 2cosleft(frac<2> ight)cosleft(frac<2> ight))
  • (cos u - cos v = -2sinleft(frac<2> ight)sinleft(frac<2> ight))

Example 2: Evaluate a Trigonometric Expression

Find the exact value of cos 75° − cos 15°.

Solution

This is a difference of cosines, so use the last formula with u = 75° and v = 15°.

$cos u - cos v = -2sinleft(frac<2> ight)sinleft(frac<2> ight)$

$cos 75° - cos 15° = -2sinleft(frac<75° + 15°><2> ight)sinleft(frac<75° - 15°><2> ight)$

Try It 2

Find the exact value of sin 255° + sin 15°.

Answer

Example 3: Solve a Trigonometric Equation

Solve on the interval [0, 2π): sin 5x + sin x = 0.

Since the equation equals zero, we could use the zero product property if the equation were a product and not a sum. So, use a sum-to-product formula with u = 5x and v = x.

(cos 3x = 0) (sin 2x = 0)
(3x = frac<π> <2>+ πn) (2x = 0 + πn)
(x = frac<π> <6>+ frac<πn><3>) (x = frac<πn><2>)

Try It 3

Solve on the interval [0, 2π): cos 3x + cos x = 0.

Answer

Example 4: Verify a Trigonometric Identity

Solution

If the fraction used terms that were multiplied together instead of added together, then they might cancel out. So, use a sum-to-product formula for the numerator and denominator with u = 3x and v = x.


Enter the Weighting Formula

The example shown in this article calculates the weighted average for a student's final mark using the SUMPRODUCT function.

The function accomplishes this by:

  • Multiplying the various marks by their individual weight factor.
  • Adding the products of these multiplication operations together.
  • Dividing the above sum by the total of the weighting factor 7 (1+1+2+3) for the four assessments.

Like most other functions in Excel, SUMPRODUCT can be entered into a worksheet using the Functions Library found on the Formulas tab. Because the weighting formula in this examples uses SUMPRODUCT in a non-standard way (the function's result is divided by the weight factor), the weighting formula must be typed into a worksheet cell.

To enter the SUMPRODUCT formula to compute a weighted average, open a blank worksheet, enter the data in rows 1 through 6 from the image above, and follow these steps:

Select cell C7 to make it the active cell (this is the location where the student's final mark will display).

Type the formula =SUMPRODUCT(B3:B6,C3:C6)/(1+1+2+3) into the cell. The formula appears in the Formula Bar.

Press the Enter key on the keyboard.

The answer 78.6 appears in cell C7 (your answer may have more decimal places).

The unweighted average for the same four marks would be 76.5. Because the student had better results for his midterm and final exams, weighing the average helped improve the overall grade.


Sum to Product and Product to Sum Formulas

The process of converting sums into products or products into sums can make a difference between an easy solution to a problem and no solution at all. Two sets of identities can be derived from the sum and difference identities that help in this conversion.

Sum to product

We can use the sum and difference formulas together to rewrite the sum of two trigonometric ratios as a product of trigonometric ratios.

Example: Calculate sin75° + sin15°.

Solution: By the formula sin a + sin b, we get:
sin75° + sin15° = 2 · sin (90°/2) · cos (60°/2) = 2 · sin45° · cos30° = 2 · √2/2 · √3/2 = √6/2

Product to sum

We can also use the sum and difference formulas to write the product of two trigonometric ratios as a sum. These new formulas are called the product to sum formulas.

Example: Calculate cos105° · cos15°.

Solution: By the formula cos a · cos b, we can write:
cos105° + cos15° = 1/2 [cos (105° + 15°) + cos (105° – 15°)]
= 1/2 [cos120° + cos90°] = 1/2 · (-1/2 + 0) = -1/4


Bonus: Sum Up Top N Values Using SEQUENCE function

The SEQUENCE function is introduced in Excel 2019 and 365. It returns a sequence of numbers. We can use it to get the top N values and then sum them up.

Generic Formula:

=SUMPRODUCT(LARGE(range,SEQUENCE(num_values,,[start_num], [steps]))))

range: The range is the range from which you want to sum top N values.

num_values: It is the number of top values that you want to sum.

[start_num]: It is the starting number of the series. It is optional. If you omit this, the series will start from 1.

[steps]: It is the difference between the next number from the current number. By default, it is 1.

If we use this generic formula to get the same result as we did in the previous example, the formula will be:

=SUM(LARGE(C2:C13,SEQUENCE(4,,3)))

It will return the value 534040.

How does it work?

It is simple. The SEQUENCE function returns a series of 4 values that start with 3 with interval 1. It returns the array <3456>. Now LARGE function returns the largest values of corresponding numbers. Finally, the SUM function sums up these value and we get our result as 534040.

So yeah guys, this is how you can sum up the top 3, 5, 10, . N values in Excel. I tried to be explanatory. I hope it helps you. If you have any doubts regarding this topic or any other excel VBA related topic, ask in the comments section below. I reply to queries frequently.

Related Articles:

Sum If Greater Than 0 in Excel | To sum only positive values, we need to sum values that are greater than 0. To do so, we will use SUMIF function of Excel.

How to Sum Last 30 Days Sales in Excel | Some times it is also summarized on standard 30 day interval. In that case, having a formula that dynamically shows the sum of the last 30 days will be a good idea. In this article, we will learn how to sum the last 30 days' values in excel.

How to sum last n columns in Excel | Sometimes given a condition i.e. when we need to get the sum of the last 5 or n numbers from the column. You can perform the solution to this problem easily using the excel functions.

How to Sum by Matching Row and Column in Excel | How do we sum values when we need to match column and row. In this article, we will learn how to do the sum of matching rows and columns.

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use Excel VLOOKUP Function | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the Excel COUNTIF Function | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use Excel VLOOKUP Function | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the Excel COUNTIF Function | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.


Excel SUMIFS with multiple OR criteria

If you want to conditionally sum values in Excel not simply with multiple OR conditions, but with several sets of conditions, you will have to use SUMIFS instead of SUMIF. The formulas will be very similar to what we've just discussed.

As usual, an example might help to illustrate the point better. In our table of fruit suppliers, let's add the Delivery Date (column E) and find the total quantity delivered by Mike, John and Pete in October.

Example 1. SUMIFS + SUMIFS

The formula produced by this approach includes a lot of repetition and looks cumbersome, but it is easy to understand and, most importantly, it works : )

=SUMIFS(D2:D9,C2:C9, "Mike", E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014") +
SUMIFS(D2:D9, C2:C9, "John", E2:E9, ">=10/1/2014", E2:E9, "<=10/31/2014") +
SUMIFS(D2:D9, C2:C9, "Pete", E2:E9, ">=10/1/2014" ,E2:E9, "<=10/31/2014")

As you see, you write a separate SUMIFS function for each of the suppliers and include two conditions - equal to or greater than Oct-1 (">=10/1/2014",) and less than or equal to Oct 31 ("<=10/31/2014"), and then you sum the results.

Example 2. SUM & SUMIFS with an array argument

I've tried to explain the essence of this approach in the SUMIF example, so now we can simply copy that formula, change the order of arguments (as you remember it is different in SUMIF and SUMIFS) and add additional criteria. The resulting formula is more compact than SUMIFS + SUMIFS:

The result returned by this formula is exactly the same as you see in the screenshot above.

Example 3. SUMPRODUCT & SUMIFS

As you remember, the SUMPRODUCT approach differs from the previous two in the way that you enter each of your criteria in a separate cell rather that specify them directly in the formula. In case of several criteria sets, the SUMPRODUCT function won't suffice and you will have to employ ISNUMBER and MATCH as well.

So, assuming that the Supplies Names are in cells H1:H3, Start Date is in cell H4 and End Date in cell H5, our SUMPRODUCT formula takes the following shape:

=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)

Many people wonder why use double dash (--) in SUMPRODUCT formulas. The point is that Excel SUMPRODUCT ignores all but numeric values, while the comparison operators in our formula return Boolean values (TRUE / FALSE), which are non-numeric. To convert these Boolean values to 1's and 0's, you use the double minus sign, which is technically called the double unary operator. The first unary coerces TRUE/FALSE to -1/0, respectively. The second unary negates the values, i.e. reverses the sign, turning them into +1 and 0, which the SUMPRODUCT function can understand.

I hope the above explanation makes sense. And even if it doesn't, just remember this rule of thumb - use the double unary operator (--) when you are using comparison operators in your SUMPRODUCT formulas.


Sum Of Product (SOP) & Product Of Sum (POS)

Sum Of Product (SOP)

Sum of Product is the abbreviated form of SOP. Sum of product form is a form of expression in Boolean algebra in which different product terms of inputs are being summed together. This product is not arithmetical multiply but it is Boolean logical AND and the Sum is Boolean logical OR.

To understand better about SOP, we need to know about min term.

Min Term

Minterm means the term that is true for a minimum number of combination of inputs. That is true for only one combination of inputs.

Since AND gate also gives True only when all of its inputs are true so we can say min terms are AND of input combinations like in the table given below.

3 inputs have 8 different combinations. Each combination has a min terms denoted by small m and its decimal combination number written in subscript. Each of these minterms will be only true for the specific input combination.

Types of Sum Of Product (SOP) Forms

There are few different forms of Sum of Product.

Canonical SOP Form

This is the standard form of Sum of Product. It is formed by O Ring the minterms of the function for which the output is true. This is also known as Sum of Min terms or Canonical disjunctive normal form (CDNF). It is just a fancy name. “canonical” means “standardized” and “disjunctive” means “Logical OR union”.

Canonical SOP expression is represented by summation sign and minterms in the braces for which the output is true.

For example, a functions truth table is given below.

For this function the canonical SOP expression is

Which means that the function is true for the min terms .

By expanding the summation we get.

Now putting min terms in the expression

F = A̅B̅C + A̅BC̅ + A̅BC + AB̅C

Canonical form contains all inputs either complemented or non-complemented in its product terms.

Non-Canonical SOP Form

As the name suggests, this form is the non-standardized form of SOP expressions. The product terms are not the min terms but they are simplified. Let’s take the above function in canonical form as an example.

F = A̅B̅C + A̅BC̅ + A̅BC + AB̅C

F = A̅B̅C + A̅B(C̅ + C) + AB̅C

F = A̅B̅C + A̅B(1) + AB̅C

F = A̅B̅C + A̅B + AB̅C

This expression is still in Sum of Product form but it is non-canonical or non-standardized form.

Minimal SOP Form

This form is the most simplified SOP expression of a function. It is also a form of non-canonical form. Minimal SOP form can be made using Boolean algebraic theorems but it is very easily made using Karnaugh map (K-map).

Minimal SOP form is preferred because it uses the minimum number of gates and input lines. it is commercially beneficial because of its compact size, fast speed, and low fabrication cost.

Let’s take an example of the function given above in canonical form.

According to the K-map, the output expression will be

F = B̅C + A̅B

This is the most simplified & optimized expression for the said function. This expression requires only two 2-input AND gates & one 2-input OR gate. However, the canonical form needs four 3-input AND gates & one 4-input OR gate, which is relatively more costly than minimal form implementation.

Schematic Design of Sum Of Product (SOP)

SOP expression implements 2 level AND-OR design in which the 1 st level gate is AND gate following the 2 nd level gate which is OR gate. Schematic design of SOP expression needs a group array of AND gates & one OR gate.

Every SOP expression has somewhat same designing i.e. all the inputs goes through AND gate and then the output of these AND gates flow through an OR gate as shown in the figure given below.

The number of inputs and the number of AND gates depend upon the expression one is implementing.

Example of designs of canonical and minimal SOP expression for a function is given below.

Conversion from Minimal SOP to Canonical SOP Form

Conversion from minimal or any sort of non-canonical form to canonical form is very simple.

As we know canonical form has min terms & min terms consists of all inputs either complemented or non-complemented. So we will multiply every term of minimal SOP with the sum of missing input’s complemented and non-complemented form. Example of conversion for the above function in minimal SOP form is given below.

F = A̅B + B̅C

The term A̅B is missing input C. So we will multiply A̅B with (C+C̅) because (C+C̅ = 1). The term B̅C is missing input A. so it will be multiplied with (A+A̅)

F = A̅B(C + C̅) + B̅C(A + A̅)

F = A̅BC + A̅BC̅ + AB̅C + A̅B̅C

Now, this expression is in canonical form.

Conversion from Canonical SOP to Canonical POS

Standard SOP expression can be converted into standard POS (product of sum) expression. For example, the function given above is in canonical SOP form

F = A̅B̅C + A̅BC̅ + A̅BC + AB̅C

The remaining terms of this function are maxterms for which output is false. These max terms are M0,M4,M6,M7. These Max terms will be used in POS expression as the product of these max terms. The Symbol of Product is ∏.

F = (A+B+C)(A̅+B+C)(A̅+B̅+C)(A̅+B̅+C̅)

The Max terms are the complement of minterms. Which is why M0=(A+B+C).

Conversion from Canonical SOP to Minimal SOP

Canonical SOP can be converted to minimal SOP. It can be converted using Karnaugh map or Boolean algebraic theorems. The K-map method is very easy and its example has been done above in the minimal SOP form.

Product of Sum

Product of Sum abbreviated for POS.

The product of Sum form is a form in which products of different sum terms of inputs are taken. These are not arithmetic product and sum but they are logical Boolean AND and OR respectively.

To better understand about Product of Sum, we need to know about Max term.

Max Term

Maxterm means the term or expression that is true for a maximum number of input combinations or that is false for only one combination of inputs.

Since OR gate also gives false for only one input combination. So Maxterm is OR of either complemented or non-complemented inputs.

Max terms for 3 input variables are given below.

3 inputs have 8 different combinations so it will have 8 maxterms. Maxterms are denoted by capital M and decimal combination number In the subscript as shown in the table given above.

In maxterm, each input is complemented because Maxterm gives ‘0’ only when the mentioned combination is applied and Maxterm is complement of minterm.

M3 = A + B̅ +C̅ DE Morgan’s law

Which is why for A=0 Max term consist A & for A=1 Max term consist A̅.

Types of Product Of Sum Forms

There are different types of Product of Sum forms.

Canonical POS Form

It is also known as Product of Max term or Canonical conjunctive normal form (CCNF). Canonical means standard and conjunctive means intersection.

In this form, Maxterms are AND together for which output is false.

Canonical POS expression is represented by ∏ and Maxterms for which output is false in brackets as shown in the example given below.

F = (A+B+C)(A̅+B+C)(A̅+B̅+C)(A̅+B̅+C̅)

The canonical form contains all inputs either complemented or non-complemented in its each Sum term.

Non – Canonical Form

The product of sum expression that is not in standard form is called non-canonical form.

Let’s take the above-given function as an example.

F = (A+B+C)(A̅+B+C)(A̅+B̅+C)(A̅+B̅+C̅)

F = (B+C) (A̅+B̅+C)(A̅+B̅+C̅)

Same but inverted terms eliminates from two Max terms and form a single term to prove it here is an example.

= AA̅+AB+AC+A̅B+BB+BC+A̅C+BC+CC

= 0+AB+AC+A̅B+A̅C+B+BC+C

= A(B+C)+A̅(B+C)+B(1+C)+C

The expression achieved is still in Product of Sum form but it is non-canonical form.

Minimal POS Form

This is the most simplified and optimized form of a POS expression which is non-canonical. Minimal Product of Sum form can be achieved using Boolean algebraic theorems like in the non-canonical example given above. Another method of achieving minimal POS form is by using Karnaugh map which is comparatively easier than using Boolean algebraic theorems.

Minimal POS form uses less number of inputs and logic gates during its implementation, that’s why they are being preferred over canonical form for their compact,fast and low-cost implementation.

Let’s take the above-given function as example

Minimal expression using K-map

The achieved expression is the minimal product of sum form. It is still Product of Sum expression But it needs only 2 inputs two OR gates and a single 2 input AND gate. However, the canonical form needs 4 OR gates of 3 inputs and 1 AND gate of 4 inputs.

Schematic Design of Product of Sum (POS)

The product of Sum expression has a specific schematic design of OR-AND. In OR-AND the inputs go through an array of OR gates which is the first level of gates, the output of the first level OR gates goes through the second level of the gate,which is an AND gate.

The number of inputs and number of gates used in this design depends upon the expression that is to be implemented.

The canonical form consists of the max number of possible inputs and gates,however, the minimal form consists of the lowest possible number of inputs and gates. The schematic design of canonical and minimal POS form is given below.

Conversion from Minimal POS to Canonical form POS

As we know the canonical form of POS has max terms and max terms contains every input either complemented or non-complemented. So we will add every sum term with the product of complemented and non-complemented missing input. Example of its conversion is given below.

(A̅+B̅) term is missing C input so we will add (CC̅) with it. (B+C) term is missing A input so we will add (AA̅) with it.

F = (A̅+B̅+CC̅) (B+C+AA̅)

F = (A̅+B̅+C)(A̅+B̅+C̅)(A+B+C)(A̅+B+C)

This expression is now in canonical form.

Conversion From Canonical POS to SOP

The product of Sum expression can be converted into Sum of Product form only if the expression is in canonical form. Canonical POS and canonical SOP are inter-convertible i.e. they can be converted into one another. Example of POS to SOP conversion is given below.

F = (A+B+C)(A̅+B+C)(A̅+B̅+C)(A̅+B̅+C̅)

In canonical form each sum term is a max term so it can also be written as:

The remaining combinations of inputs are minterms of the function for which its output is true. To convert it into SOP expression first we will change the symbol to summation (∑) and use the remaining minterm.

Now we will expand the summation sign to form canonical SOP expression.

F = A̅B̅C + A̅BC̅ + A̅BC + AB̅C

Min terms are complement of Max terms for the same combination of inputs.

Canonical to Minimal POS

A canonical Product of Sum expression can be converted into Minimal Product of sum form by using Karnaugh map (K-map). Another method for converting canonical into minimal is by using Boolean algebraic theorems.

The use of K-map is very easy that is why K-map is preferred. For minimal POS expression, 0’s in K-map are combined into groups and the expression we get is complemented since the groups were made of ‘0’s. Its example has been done above.


How to use SUM & IF function instead for SUMPRODUCT or SUMIFS function in Excel

In this article, we will learn How to use IF function instead of SUMPRODUCT and SUMIFS function in Excel.

In simple words, when working with a long scattered dataset, sometimes we need to find the sum of numbers with some criteria over it. For example, finding the sum of salaries in a particular department or having multiple criterias over date, names, department or can even numbers data like salaries below value or quantity above value. For this you usually use the SUMPRODUCT or SUMIFS function. But you wouldn't believe, you perform the same function with Excel basic function IF function.

How to solve the problem?

You must be thinking how is this possible, to perform logical operations over table arrays using IF function. IF function in excel is very useful, It will get you through some difficult tasks in Excel or any other coding languages. IF function tests conditions on array corresponding to required values and returns the result as array corresponding to True conditions as 1 and False as 0.

For this problem, we will be using the following functions :

We will be requiring these above functions and some basic sense of data operation. logical conditions on arrays can be applied using logical operators. These logic operators work on text and numbers both. Below here is the generic formula. curly braces is the magic tool to perform array formulas with IF function.

Note: For curly braces ( ) Use Ctrl + Shift + Enter when working with arrays or ranges in Excel. This will generate Curly Braces on the formula by default. DO NOT try to hard code curly braces characters.

Logical 1 : tests condition 1 on array 1

Logical 2 : tests condition 2 on array 2 and so on

sum_array : array, operation sum is performed

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we have data of delivered products to different cities along with corresponding category fields and quantities. Here we have the data and we need to find the quantity of cookies sent to Boston where the quantity be greater than 40.

Data table and criteria table are shown in the above image. For understanding purpose we used named ranges for the used arrays. Named ranges are listed below.

City defined for array A2:A17.

Category defined for array B2:A17.

Quantity defined for array C2:C17.

Now you are ready to get the desired result using the below formula.

  1. City ="Boston" : checks the values in city range to match with "Boston".
  2. Category="Cookies" : checks the values in Category range to match with "Cookies".
  3. Quantity > 40 : checks the values in Quantity range to ma
  4. Quantity be array where sum is required.
  5. IF function checks all criteria and asterisk char (*) multiples all the array results.
  1. Now IF function only returns the quantities corresponding to the 1s and rest are ignored.
  2. SUM function returns the SUM.

Now the quantity corresponding to 1’s only adds up to get the result.


As you can see, quantity 43 is returned but there are three cookie orders delivered to "Boston" having quantity 38, 36 and 43. We needed a sum of quantity where quantity be above 40. So the formula returns 43 only. Now use other criteria to get the SUM Quantity for City : "Los Angeles" & Category : "Bars" & Quantity be less than 50.

As you can see, the formula returns the values 86 as result. Which is the sum of 2 orders satisfying the conditions having quantity 44 & 42. This article, illustrates how to replace a nested IF formula with a single IF in an array formula. This can be used to reduce complexity in complex formulas. However, This particular problem could be easily solved with SUMIFS or SUMPRODUCT function.

Use of SUMPRODUCT function:

SUMPRODUCT function returns the sum of corresponding values in the array. So we will get the arrays to returns 1s a the True statement values and 0s to the False statement values. So last sum will be corresponding where all statements stands True.

= SUMPRODUCT ( -- (City = "Boston") , -- (Category = "Cookies") , -- (Quantity > 40) , Quantity )

-- : operation used to convert all TRUEs to 1s and False to 0.

SUMPRODUCT function rechecks the SUM of quantity returned by the SUM and IF function explained above.

Similarly for the second example the result stands the same.

As you can see SUMPRODUCT function can perform the same task.

Here are all the observational notes regarding using the formula.

  1. The sum_array in the formula only works with numbers.
  2. If the formula returns #VALUE error, check for the curly braces must be present in the formula as shown in the examples in the article.
  3. Negation (--) char changes values, TRUEs or 1s to FALSEs or 0s and FALSEs or 0s to TRUEs or 1s .
  4. Operations like equals to ( = ), less than equal to ( <= ), greater than ( > ) or not equals to ( <> ) can be performed within a formula applied, with numbers only.

Hope this article about How to use IF function instead of SUMPRODUCT and SUMIFS function in Excel is explanatory. Find more articles on Summing formulas here. If you liked our blogs, share it with your fristarts on Facebook . And also you can follow us on Twitter and Facebook . We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

How to use the SUMPRODUCT function in Excel : Returns the SUM after multiplication of values in multiple arrays in excel.

SUM if date is between : Returns the SUM of values between given dates or period in excel.

Sum if date is greater than given date: Returns the SUM of values after the given date or period in excel.

2 Ways to Sum by Month in Excel: Returns the SUM of values within a given specific month in excel.

How to Sum Multiple Columns with Condition: Returns the SUM of values across multiple columns having condition in excel.

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.