Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?

Date:2022-1-18 Author:Sandra

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.  

Syntax

=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?

How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?

Enter =SUMPRODUCT(C3:C7, D3:D7) in cell C9 and press Enter key.

How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?
How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?

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?

How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?
How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?

(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.

How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?
How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?

The two ways both get result #VALUE!.

How to Use the SUMPRODUCT Function in Excel -Super Easy Tutorial?

But the SUMPRODUCT Function goes well. So, When non-numeric cells are present, the formula ignores them.

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

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