Creating cross-totals with the SUMIFS function and mixed references

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