Meet fellow New Skills Academy students in our study group. Join now!

Microsoft Excel Intermediate Certification

100%
Module 1: SumProduct Formula

Module 1: SumProduct Formula

Duration: Approx 20 minutes

1.1 SumProduct Function


This function will multiply corresponding components in the given arrays, and will then return the sum of the products.
The formula for this function is as follows: =SUMPRODUCT (array1, [array2], [array3],…)

Array1 is the only one that is required. This is the first argument (two columns of numeric data) whose components you want to multiply and then add together.

1.2 Video Instruction



SumProduct Function – 2m 12s






1.3 Task


Estimated Time: 10 minutes

Download the worksheet that accompanies this video by clicking below. Once downloaded, practice using the SumProduct function in Excel using the video above as a reference.


Download Worksheet

Option #2: Worksheet without formula


Download Worksheet


Additional Exercises: please click on the following link to download extra exercises to practice throughout the course. Answers can be found by scrolling further down the spreadsheets (where applicable): Download Additional Exercises


Next >>

Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


1
Module 1: SumProduct Formula
20 minutes
2
Module 2 : Data Validation With Date
20 minutes
3
Module 3 : Data Validation with Whole Numbers
20 minutes
4
Module 4 : Data Validation with List Feature
20 minutes
5
Module 5 : ABS Function
20 minutes
6
Module 6 : CHAR Function
20 minutes
7
Module 7 : CLEAN Function
20 minutes
8
Module 8 : CODE Function
20 minutes
9
Module 9 : EXACT Function
20 minutes
10
Module 10 : AVERAGEA Function
20 minutes
11
Module 11 : AVERAGEIF
20 minutes
12
Module 12 : AVERAGEIFS Function
20 minutes
13
Module 13 : CELL Function With 10 Variations
20 minutes
14
Module 14 : LARGE Function
20 minutes
15
Module 15: MAX Function
20 minutes
16
Module 16: MEDIAN Function
20 minutes
17
Module 17: MIN Function
20 minutes
18
Module 18: MODE Function
20 minutes
19
Module 19 : N Function
20 minutes
20
Module 20 : RANDBETWEEN Function (Insert Random Numbers)
20 minutes
21
Module 21 : RANK Function
20 minutes
22
Module 22 : Remove Duplicate Values
20 minutes
23
Module 23 : ROUND Function Up/Down
20 minutes
24
Module 24 : Add Custom Background Image
20 minutes
25
Module 25: Change Text To Lower Case
20 minutes
26
Module 26: Change Text To Upper Case
20 minutes
27
Module 27: Change Text To Proper Case
20 minutes
28
Module 28: Insert Special Character
20 minutes
29
Module 29: Use Autofill : A Time Saving Trick
20 minutes
30
Module 30: Treemap Chart
20 minutes
31
Module 31: Sunburst Chart
20 minutes
32
Module 32: Use Tables
20 minutes
33
Module 33: Use Templates
20 minutes
34
Module 34: Use Themes
20 minutes

Module 5 : ABS Function

 

 

 

5.1 ABS function: absolute number without + or - resut.                       ex:=ABS()

Visit Module

Module 12 : AVERAGEIFS Function

AVERAGEIFS caters for multiple criteria, AVERAGEIF iz just for one cirterion/condition.

Visit Module

Module 13 : CELL Function With 10 Variations

The CELL function is an informative formula that tells you about the contents, location, formatting, and properties of a specific cell.

Visit Module

Module 14 : LARGE Function

LARGE formula allows you to find the position of a rank of a large set of numbers.

Visit Module

Module 15: MAX Function

MAX gives largest number in a set of values

Visit Module

Module 19 : N Function

=N converts anything to numbers if possible

Visit Module

Module 20 : RANDBETWEEN Function (Insert Random Numbers)

To do for a whole table, highlight whole table, do =RANDBETWEEN(x,y) then select control+Enter to randomise the entire selection rather than just one cell.

Visit Module

Module 22 : Remove Duplicate Values

Data Tab -> remove duplicate.  no code needed.

Visit Module

Module 25: Change Text To Lower Case

First create a column or row for the return value or information.Then input the formula as follows: =LOWER(reference value) This will change all of the text to lowercase.
 
Or do =UPPER for upper.
 
=PROPER capitalizes the first letter of each word only.
 
Visit Module

Module 29: Use Autofill : A Time Saving Trick

Clicking 'Fill' the 'Justify' will narrow/ broaden text to fit into fewer/more cells.

Visit Module

Module 30: Treemap Chart

A Treemap chart lets you view your data in a hierarchical manner, which makes it easy to identify patterns in terms of popularity, prices, volume, and others. 
Treemap charts divide up categories into different-sized rectangles that are proportional to the amount or number (units) they represent.
Visit Module

Module 28: Insert Special Character

Special characters 

Click on Insert and then click on Symbols.

Visit Module

Frequently Asked Questions

To progress on to the next module of your course scroll to the bottom of the current module and press the “NEXT” button. This will take you directly to the following module.

Once you have completed the last module of the course please click on the button at the bottom of the module that says “START”. This will take you to the test.

The pass mark for the end of course test is 70%

Yes, you can re-take your test as many times as you need to.

No, once you have passed a test it is not possible to retake it.

When logging on to your course directly through our site you have lifetime access.

Click on the “My Profile” button at the top of any page on our site. This will take you to your profile page. Once there scroll down to the section headed “Edit Your Profile” and enter your new password.

Please click here to contact our student support team

If you would like to go back and view a completed module you can do this by clicking the “Course Modules” option from the side bar menu of your course. This will take you to a list of the modules contained within your course. Simply select the module you wish to study again.

Please click here to find out how to save your course modules as PDF files

Worksheets

Module 1: SumProduct Formula

Module 1: SumProduct Formula

Module 2 : Data Validation With Date

Module 2 : Data Validation With Date

Module 3 : Data Validation with Whole Numbers

Module 4 : Data Validation with List Feature

Module 4 : Data Validation with List Feature

Module 5 : ABS Function

Module 5 : ABS Function

Module 6 : CHAR Function

Module 6 : CHAR Function

Module 7 : CLEAN Function

Module 7 : CLEAN Function

Module 8 : CODE Function

Module 8 : CODE Function

Module 9 : EXACT Function

Module 9 : EXACT Function

Module 10 : AVERAGEA Function

Module 10 : AVERAGEA Function

Module 11 : AVERAGEIF

Module 11 : AVERAGEIF

Module 12 : AVERAGEIFS Function

Module 12 : AVERAGEIFS Function

Module 13 : CELL Function With 10 Variations

Module 13 : CELL Function With 10 Variations

Module 14 : LARGE Function

Module 14 : LARGE Function

Module 15: MAX Function

Module 15: MAX Function

Module 16: MEDIAN Function

Module 16: MEDIAN Function

Module 17: MIN Function

Module 17: MIN Function

Module 18: MODE Function

Module 19 : N Function

Module 19 : N Function

Module 20 : RANDBETWEEN Function (Insert Random Numbers)

Module 20 : RANDBETWEEN Function (Insert Random Numbers)

Module 21 : RANK Function

Module 21 : RANK Function

Module 22 : Remove Duplicate Values

Module 23 : ROUND Function Up/Down

Module 23 : ROUND Function Up/Down

Module 23 : ROUND Function Up/Down

Module 23 : ROUND Function Up/Down

Module 24 : Add Custom Background Image

Module 25: Change Text To Lower Case

Module 25: Change Text To Lower Case

Module 26: Change Text To Upper Case

Module 26: Change Text To Upper Case

Module 27: Change Text To Proper Case

Module 27: Change Text To Proper Case

Module 28: Insert Special Character

Module 29: Use Autofill : A Time Saving Trick

Module 30: Treemap Chart

Module 31: Sunburst Chart

Module 32: Use Tables

Module 32: Use Tables

Module 33: Use Templates

Module 34: Use Themes

Module PDF's

Module 1: SumProduct Formula

Module 2 : Data Validation With Date

Module 3 : Data Validation with Whole Numbers

Module 4 : Data Validation with List Feature

Module 5 : ABS Function

Module 6 : CHAR Function

Module 7 : CLEAN Function

Module 8 : CODE Function

Module 9 : EXACT Function

Module 10 : AVERAGEA Function

Module 11 : AVERAGEIF

Module 12 : AVERAGEIFS Function

Module 13 : CELL Function With 10 Variations

Module 14 : LARGE Function

Module 15: MAX Function

Module 16: MEDIAN Function

Module 17: MIN Function

Module 18: MODE Function

Module 19 : N Function

Module 20 : RANDBETWEEN Function (Insert Random Numbers)

Module 21 : RANK Function

Module 22 : Remove Duplicate Values

Module 23 : ROUND Function Up/Down

Module 24 : Add Custom Background Image

Module 25: Change Text To Lower Case

Module 26: Change Text To Upper Case

Module 27: Change Text To Proper Case

Module 28: Insert Special Character

Module 29: Use Autofill : A Time Saving Trick

Module 30: Treemap Chart

Module 31: Sunburst Chart

Module 32: Use Tables

Module 33: Use Templates

Module 34: Use Themes

My Notes