Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using SUMIF, COUNTIF, and related functions for quick data analysis

From: Managing and Analyzing Data in Excel 2010

Video: Using SUMIF, COUNTIF, and related functions for quick data analysis

The SUMIF/COUNTIF family of functions, introduced in Excel 97, now include four new functions in Excel 2007 and they give you lots of tools for tabulating a variety of statistical totals when you have database type lists. The functions and questions are listed in Column J here. Normally you wouldn't see these listed just like this, and I've separated them because they're more or less in groupings here. SUMIF, COUNTIF and AVERAGEIF. AVERAGEIF is new in Excel 2007.

Using SUMIF, COUNTIF, and related functions for quick data analysis

The SUMIF/COUNTIF family of functions, introduced in Excel 97, now include four new functions in Excel 2007 and they give you lots of tools for tabulating a variety of statistical totals when you have database type lists. The functions and questions are listed in Column J here. Normally you wouldn't see these listed just like this, and I've separated them because they're more or less in groupings here. SUMIF, COUNTIF and AVERAGEIF. AVERAGEIF is new in Excel 2007.

And these functions you can almost figure out what they do by their names. This list here is substantial. It might grow, it might shrink. And we might want to know, for example, simple question, how many half-time people do we have, how many full-time people do we have? So start off actually with COUNTIF and use this function right here, =COUNTIF. Two arguments. Where are we looking? Now you certainly can and I certainly could here highlight all of the cells in Column D, but it's a lot simpler to simply click Column D instead.

So instead of doing all that let's just click. And what's great about this capability too, or this approach to the function is that if we add names to the list or if we take them out we don't need to change the way this formula is being set up. So using an entire column here makes sense. Where are we looking? Column D. What are we looking for? Full Time. Now if I don't have that phrase nearby in a cell, although I do to the right here, I'll simply put in double- quote and type in "Full Time". And we are all done, Enter.

How many full-time people do we have here? 393. Obviously we're going to keep that, adjust the display of the format. If we do have the data nearby, possibly instead you would just click on that cell. So how many full-time people do we have? And if we somehow found that to be valuable and we have the other kinds of statuses listed next to this we could clean up the display a little bit by decreasing the decimals and simply drag this down a few more cells, there we go.

And so we've got a Half Time count and an Hourly count and a Contract. So COUNTIF is pretty straightforward and easy to use, and it does provide us with good information about our data. And if you want something like this to be setup along with your data it's not a bad idea to put off to the right. But with one slight exception it might not be so good. If you do a lot of filtering, which of course does hide rows, some of this data may be hidden at different times. Now the other option possible is to put this above your data, but then it makes these kinds of formulas not quite as easy to write, because you couldn't be able to use entire columns there.

All right, without going through all the variations here but just a few more, the SUMIF function does go a step further and it allows us to tabulate some data from another range. And when we are using different ranges here they must be parallel. So let's say that instead of counting how many people that we have that are full-time, we want to know their total salaries. So SUMIF has a third argument, comma, and in this example it's Column G so we'll just G, all done.

Total salaries of our full-time people. There too we could copy that down if wanted the information for the others. And initially these two functions were around for quite a while, a lot of clamoring for this function, new in Excel 2007, and you can imagine what it does. Real simply quickly here we'll just simply put in-- we want to know the average salary of for example, the people that are full time, and there it is, and we could do the same for the others as well too, so AVERAGEIF.

In prior versions before Excel 2007, if you wanted to use multiple criteria you had to use array formulas but these three new functions here are just that they are exactly nearer the others except they end in S. I'll start with this one. And I am going to point out here that actually SUMIFs and AVERAGEIFs are little different than COUNTIFs and start with this idea. We need to add up the salaries, not only the people who are full time, but the people who are full time and have a certain job rating.

And we can have multiple sets of criteria. So the SUMIFs function begins with what it is we are trying to add up. So the order of things here might get a little confusing if you just remember what we've done in the previous ones. In the other functions that we just saw the data to be tabulated came last, not comes first. So we want to be tabulating salaries, comma, and now in pairs various criteria. And we can have one pair, two pair, three pair, quite a few in fact.

The first pair might be Status, comma, Full Time. That's in cell L2 so I'll just refer to that. We could hit Enter right here and simply get total salary of our full time people. We already did that with SUMIF, but to expand on the idea here, SUMIFs allows us multiple criteria, so what else are we looking for here? People who also have a job rating, comma and because this is a number we don't have to put it in quotes, those who have a job rating of 5.

We could have another pair. So the first pair indicates we want to be looking at those who are full-time and, even though we don't see the word and, second pairing here is job rating of 5. We press Enter. The total salaries of our full yime people who have a job rating of 5. It might be more interesting and more valuable to be able to do this as an actual average. So why not just pull this down here and readjust it, put it next to average, make a change? We want to know the average salary of all those people in our list here who are full time.

So we've got a pairing here who are full time and who have a job rating of 5. There is the average salary for that group. Now again, there could be following the five, a comma, a pair of criteria, two more of these, another pair, another pair, on and on and on and on, without long cumbersome formulas. So these are great tools for tabulating data, particularly adept at handling information across lists. SUMIFs, COUNTIFs, and AVERAGEIFs.

If you want more examples you might also want to check out the course on Advanced Formulas and Functions either for Excel 2007 or Excel 2010. They are identical in the two different versions.

Show transcript

This video is part of

Image for Managing and Analyzing Data in Excel 2010
Managing and Analyzing Data in Excel 2010

27 video lessons · 22343 viewers

Dennis Taylor
Author

 
Expand all | Collapse all
  1. 1m 32s
    1. Welcome
      54s
    2. Using the exercise files
      38s
  2. 25m 18s
    1. Sorting from the Sort menu
      4m 37s
    2. Sorting from the toolbar
      4m 2s
    3. Multi-key sorting
      3m 4s
    4. Sorting based on the order of data in custom lists
      4m 44s
    5. Sorting by color font, color background, or icon
      3m 57s
    6. Sorting columns
      2m 11s
    7. Sorting data in random order
      2m 43s
  3. 19m 1s
    1. Using single- and multiple-column text filtering
      5m 8s
    2. Taking a look at special numeric filters
      1m 54s
    3. Harnessing special date filters
      2m 5s
    4. Creating a top-ten list by value or percent
      3m 11s
    5. Creating custom filters
      1m 40s
    6. Copying and sorting filtered lists
      3m 7s
    7. Recognizing the limitations of standard filtering
      1m 56s
  4. 11m 16s
    1. Setting up subtotals
      4m 20s
    2. Creating multiple levels and copying subtotals
      6m 56s
  5. 13m 22s
    1. Using the Advanced Filter for complex OR criteria
      4m 30s
    2. Using the Advanced Filter for complex multiple-field criteria
      5m 37s
    3. Using the Advanced Filter to create unique lists from repeating field data
      3m 15s
  6. 10m 44s
    1. Using the Remove Duplicates command
      2m 30s
    2. Using a specialized array formula to identify data that's been duplicated
      5m 10s
    3. Using an array formula to count the number of unique items in a list
      3m 4s
  7. 10m 31s
    1. Using SUMIF, COUNTIF, and related functions for quick data analysis
      6m 48s
    2. Using database functions like DSUM, DAVERAGE, and DMAX
      3m 43s
  8. 34s
    1. Next steps
      34s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Managing and Analyzing Data in Excel 2010.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.