Join Dennis Taylor for an in-depth discussion in this video Creating cross-totals with the SUMIFS function and mixed references, part of Excel Tips Weekly.
- When you're trying to tabulate totals from a worksheet, one thought could be, we can go to the Data tab. And if we do sort the data, right now, it's not sorted but if it were sorted, we could consider using the Subtotal feature. And it's ideal for tabulating a single set of totals. What it's not adept at is multiple sets of totals, what we sometimes call cross totals. Now this list is about 900 rows. We'd like to know how many dishwashers Bill Babowsky has sold, how many dryers Shelley Levene has sold and so on.
So looking at the data here, we could as we explore this data, consider the possibility of a pivot table. That might serve our needs. What if we're not too adept at pivot tables. Or maybe, if we think that's doing a lot more than we actually need to do. Could we come up with totals here by way of formulas? Well, we can. The question is which function or set of formulas might we use. You may have heard of the COUNTIF function. There's a family of functions related to it. There's also a SUMIF, an AVERAGEIF.
And there are three plural variations: COUNTIFS, SUMIFS, AVERAGEIFS. And if you have Office 365 installed and if you do have the latest updates, you also have access to a MAXIFS function and a MINIFS function. Now if we're going to be using this in Column K here, I want to zoom in to make this large and clear. And as we do this, we don't need to really being seeing Column C, D, and E. So I'm going to drag across those, right-click and Hide those columns.
And then zoom in a bit. Ultimately, we'll be putting formulas in all of these cells here but initially, let's just focus on Cell K1 as we use the SUMIFS function. Equals SUMIFS, left parethesis, begins with the data we're trying to tabulate. We want to know how many items have been sold here. We're looking in Column F. And because we will be copying this function into other cells to the right and downward, we want that reference to Column F to stay the same, we make it absolute by simply pressing the function key F4.
The reference to Column F will remain intact as we copy this function into other cells. Now after the sum range, we now put in a potential series of criteria ranges. There are pairs of them. They come in pairs. And we can have as many as 127. What are we looking for here? All the Bill Babowsky entries. So where do we look? We're looking in Column A. That's where the salesperson's names are. And here too. We need this reference to be absolute. Press the function key F4, comma.
Now we always want to be looking, at least in this cell, for Bill Babowsky specifically. So we click on Cell J2. Now we will be copying this formula, downward and rightward. What do we need to make sure of here? All of the names that we will be using will always come out of Column J. We want the Column J portion of the function to be absolute but not the row. So what do we do? Press the function key F4 repeatedly until we see the display of $J2.
No dollar sign in front of the two, comma. Now we not only want to be looking at Bill Babowsky. We want to narrow this by looking at only when the products, for the moment, are dishwashers. So now we select Column B. And that too needs to be an absolute reference. Press the function key F4, comma. And we want this reference to be dishwashers. That's currently in Cell K1. So we'll click there and all of our references here and our formulas to the actual item is in Row 1.
So now we want to be changing the K1 reference so that is reads K$1. And we can certainly type this manually but F4 makes it easy for us to simply find that particular display. There it is. K$1. So as I press Enter, we will have an answer that will give us the total number of dishwashers sold by Bill Babowsky. And there it is, right there. And I'll double-click to copy this down the column. Zoom back a little bit. So we can see the remainder of the cells there.
Highlight all these. And from the Fill handle in the lower right hand corner, drag rightward. Now do I have any cross-checking capability? Those cells are all highlighted. The total down here in the Status Bar, 8,111. If I click Column F, what do we see at the bottom of the screen? That same number. 8111. Now, to be a little bit more precise about this, ideally, we should sort the data. We could do that relatively quickly. Let's go a little bit deeper by simply sorting the data.
Let's first click in Column B. There's a quick A-Z Sort we can do off the Data tab in the ribbon. And then click in Column A, A-Z. Dishwashers aren't on top but if we go find dishwashers here. Here they are for Bill Babowsky. Let's just highlight these numbers. As we highlight all of those, we see the total down there is 94 and there's the number we saw right there. So that's probably a good enough check. Once again, exposing this formula, I'll double-click. Relatively sophisticated, a little bit tricky.
But ultimately, not rocket science as we sometimes say. It's workable. Now by way of a pivot table, you can come to the same conclusion. You can see the same numbers without formulas. So in a certain sense, maybe that's better but on the other hand, maybe you don't need the pivot table. I think it's worth exploring how these functions work and I think you can see the real value of this, the SUMIFS function used along with relative references.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
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.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating cross-totals with the SUMIFS function and mixed references