Practice Video: Adding with Criteria (SUMIFS)

Loading...

En provenance du cours de Macquarie University

Excel Skills for Business: Intermediate I

486 notes

Macquarie University

Excel Skills for Business: Intermediate I

486 notes

Cours 2 sur 4 dans Specialization Excel Skills for Business

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In the United States alone, millions of job advertisements requiring Excel skills are posted every day. Research by Burning Glass Technologies and Capital One shows that digitals skills lead to higher income and better employment opportunities at a time when digital skills job are growing much faster than non-digital jobs. In this second course of our Excel specialization Excel Skills for Business you will build on the strong foundations of the Essentials course. Intermediate Skills I will expand your Excel knowledge to new horizons. You are going to discover a whole range of skills and techniques that will become a standard component of your everyday use of Excel. In this course, you will build a solid layer of more advanced skills so you can manage large datasets and create meaningful reports. These key techniques and tools will allow you to add a sophisticated layer of automation and efficiency to your everyday tasks in Excel. Once again, we have brought together a great teaching team that will be with you every step of the way. Prashan and Nicky will guide you through each week (and I am even going to make a guest appearance in Week 5 to help you learn about my favourite tool in Excel - shh, no spoilers!). Work through each new challenge step-by-step and in no time you will surprise yourself by how far you have come. This time around, we are going to follow Uma's trials and tribulations as she is trying to find her feet in a new position in the fictitious company PushPin. For those of you who have done the Essentials course, you will already be familiar with the company. Working through her challenges which are all too common ones that we encounter everyday, will help you to more easily relate to the skills and techniques learned in each week and apply them to familiar and new contexts.

À partir de la leçon

Summarising Data

Graduate to advanced formulas in this module. Learn how you can use functions like COUNTIFS to extract information from data, as well as generate graphical representations of it.

Practice Video: COUNT functions4:26

Practice Video: Counting with Criteria (COUNTIFS)5:09

Practice Video: Adding with Criteria (SUMIFS)6:04

Practice Video: Sparklines4:24

Practice Video: Advanced Charting5:16

Practice Video: Trendlines4:26

Week 4 Wrap-Up1:12

Rencontrer les enseignants

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

Uma has been asked to help work out the total sales brought in

by each account manager.

The COUNTIFS function will allow her to count the number of sales, but

not add up their total value.

For this operation, we need the SUMIFS function instead.

But rather than counting the number of times it find cells that meet certain criteria

in a range, it adds up the corresponding values in those cells instead.

Let's try it out.

We've already clicked on the sales dash worksheet from the previous video, so

now click on cell B21.

We want to sum the total sales for Connor Betts.

So start by typing =SUM, down arrow twice to highlight SUMIFS.

Make sure you choose SUMIFS plural, instead of SUMIF singular.

Older versions of Excel only have SUMIF,

which is okay for today, but it is less powerful.

Tab to choose the function.

Observe the prompts.

The first thing it is asking for, is our sum range.

These are the values we want to add.

We have already named our ranges in the previous video, so

just type tot and tab to select Total.

Comma to specify the next argument, which is the criteria range.

Type ac and tab to select Account_Manager, which is our criteria range.

Comma to supply the next argument, which is finally the criteria.

Click on Connor Betts in cell A21, and

Ctrl+Enter to commit the function but still stay in the cell.

Connor Betts sales in total were $135,493.75.

Great, double click on the fill handle to do this for each of the account managers.

As you can see, the summarized data is now available for us, and

it's actually in the chart that has already been embedded into this dashboard.

SUMIFS is ideal for summarizing such data.

So we have seen how we can summarize data using a single criteria.

But Uma has also been asked to get total sales by each manager, for each year.

That means we have two criteria.

You can have up to 127 criteria, but

I hope you never have to create that function.

Let's start with 2 criteria.

Clicking cell C21 and type =SUM, down arrow,

down arrow, tab to select SUMIFS.

Type Tot, tap to select Total as the sum range.

Comma to specify the next argument which is account manager,

ac tab, comma followed by the criteria,

which is the account manager's name, Connor Betts.

Comma to specify the second criteria.

Note that the program offers the option of a second criteria range.

So let's type ord and double click Order_Year to select that.

Comma, to set next argument which is the criteria,

the year which is in cell C21,

Ctrl+Enter to commit the function but still stay in the cell.

Well done.

We're not quite there yet, because we want to drag the formula down,

as well as drag the formula across, and we're getting zeros everywhere, why?

The problem is relative cell references.

When we drag across, we need column A to be fixed, but relative when we drag down.

And vice versa for row 20.

To do this, we have to use a mixed reference.

Click on cell C21, to get the formula in the formula bar.

Go across to the formula bar, and click anywhere in A21.

While this cursor is on A21, press F4 and see what happens.

It's changed to an absolute cell reference.

Press F4 again, and press F4 a third time.

The $ is now only in front of A, which means the column is locked.

But not in front of the 21, which means that the row is relative.

Click on C20, press F4 once to get an absolute cell reference.

Press F4 again, the $ is in front of 20, which means row 20 is locked.

But there's no $ in front of the C, which means the column is relative.

Ctrl+Enter to commit our changes.

Drag the formula down to get that for all of their account managers.

And drag that across to get that for each year.

Great, we actually have values in all of our cells.

Mixed cell references take a bit of getting used to.

But if you get confused, just try one way, and

if it doesn't work, do it the other way.

Remember the $ locks whatever comes just after it.

And remember practice makes permanent.

Review mixed cell references in the toolbox this week,

to make sure you understand how they work.

We now know how to use both COUNTIFS and SUMIFS,

to summarize data that meet one or more criteria.

If you're feeling adventurous, try out AVERAGEIFS.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play