In our today’s tutorial, we’ll learn about a new Function. That’s SUMPRODUCT Function. Understanding the syntax is essential if you want to master the use of the function.
=SUMPRODUCT (array1, array2, array3,…)
How does this function get the result? There is a sheet that contains product names, unit prices, and sales.
If you want to get the total sales. We need to calculate 1.2 *62+5*47+93.8*31… right?
Enter =SUMPRODUCT(C3:C7, D3:D7) in cell C9 and press Enter key.
Wow, we got the result so quickly.
Brainstorming time now. Can I enter =SUMPRODUCT(C3:C7*D3:D7) or =SUM (C3:C7*D3:D7) in cell C9?
(We need to press Ctrl, Shift and Enter keys together here)
We’ll get exactly the same result, but what’s the difference between these three different ways?
Now, we change the unit price of the pen to unknown.
The two ways both get result #VALUE!.
But the SUMPRODUCT Function goes well. So, When non-numeric cells are present, the formula ignores them.