It’s not often that I can combine two solutions into a single example file, but this month I can. First, Kay wants to summarize transactions by the month using a matrix of SUMIFS() functions. Second, Jan is confused by Table column references. Referencing them is easy enough, but she wants to use absolute column references and unless you know how, you’re probably not going to luck into the right syntax. Using the same example data set, I can help them both.

I’m using Office 2007 and Windows 7. You can work with any simple data set or download the demonstration .xlsx file. Neither solution is supported by Excel 2003.

SUMIFS() matrix

Kay wants to conditionally sum a column of values in a summarizing matrix. Besides totaling the values by supplier, she wants a subtotal for each supplier by the month. For that, she can use SUMIFS(). Figure A shows a simple list of transactions for invoiced amounts and payments over a three-month period. (When applying this solution to your own work, you can extend the summarizing matrix to a full 12 months.) Amounts due and payments are both tracked in column D, but payments are entered as negative values.

Figure A

In this example, we have a simple list of transactions for three months.

The SUMIF() function in column H returns a subtotal for each supplier (column C):

=SUMIF($C$4:$C$34,$G4,$D$4:$D$34)

The SUMIF() function uses the following syntax:

SUMIF(range, criteria, [sumrange])

where range identifies the cells you want to evaluate conditionally, criteria identifies the condition, and sumrange identifies the values you want to sum. This last argument is optional; when omitted, the function sums the values in range. In Kay’s version, the function sums the values in column D only when the corresponding supplier value in column C matches the (matrix) value in column G. Kay wants to build a three-month summary matrix, and that’s where SUMIFS() comes in. (This function isn’t supported by Excel 2003 and earlier.)

Before I tackled the matrix, I added the following SUBTOTAL() function (Figure B) to the transaction side (D36):

=SUBTOTAL(9,D4:D34)

This function isn’t required but notice that the result matches Kay’s grand total in H11. We’ll use SUBTOTAL() with simple filtering to check the matrix functions we’ll add next. I find it handy to check my work because I’m not a math or accounting expert. Checks like this one are easy to incorporate into your work files and can help you spot errors that might otherwise go unnoticed.

Figure B

The SUBTOTAL() function updates as you filter records.

Now, let’s work through a quick example to see how to use a filter to check your work:

  1. Click anywhere inside the data set.
  2. Click the Data tab and then click Filter in the Sort & Filter group.
  3. Check the total amount in the matrix for Driscells; it’s $235.93.
  4. From the Supplier dropdown (header cell), uncheck all the suppliers, check only Driscells (Figure C), and click OK. The SUBTOTAL() function (Figure D) evaluates only the filtered records and returns the same sum as Kay’s in column H.
  5. To clear the filter, click Clear in the Sort & Filter group.

Figure C

Driscells is the only selected supplier.

Figure D

The SUBTOTAL() function returns the same sum as the one in column H.

You can convert the data to a Table and enable the Total Row to get the same effect. The Table object doesn’t support all Excel’s built-in features, but I recommend using them when you can. (The downloadable demonstration .xlsx file contains a Table example.)

You can’t see the (complete) matrix any longer because the filter hides rows. How you plan to use the data is an important consideration when deciding where to put a summarizing matrix such as this one. You might not use filtering–and even if you do, it might not matter if the matrix collapses and expands along with the filter. I positioned the two components this way to simplify the example, but it might not be the best configuration for a working file. Whether you add the filtering component is up to you.

So far, so good. We know that Kay’s totaling function in column H is correct. To evaluate subtotals by supplier and month, we’ll use SUMIFS(), which uses the following syntax:

SUMIFS(sumrange, criteriarange1, criteria1, [criteriarange2, criteria2], …)

This function is similar to SUMIF(), but allows multiple conditions. It’s important to note that the arguments are arranged differently from SUMIF().

We need to match the dates in column B to month values but the headers in the matrix contain text values. Replace the header values in J3:L3 with 8, 9, and 10 (see Figure E). These values represent the months August, September, and October.

Figure E

Replace the header text with values and add a helper column for month values.

We can’t use the dates in column B because we need months, not full dates. You might consider adding a Month() function to the criteriarange argument, but that argument accepts only a valid range, so that won’t work. Instead, insert a column to the right of the transaction data set and enter the following MONTH() function into F4 and copy it to the remaining records (F5:F34) as shown in Figure E:

=MONTH(B4)

Now we’re ready to build the matrix. To do so, enter the following function into J4 and copy it to the remaining matrix (J4:L10):

=SUMIFS($D$4:$D$34,$C$4:$C$34,$H4,$F$4:$F$34,J$3)

The SUMIFS() functions shown in Figure F return the monthly subtotals for each supplier.

Figure F

The SUMIFS() function adds a second condition: the month.

Let’s use the SUBTOTAL() function we added to the transaction side to check the matrix function. First, filter the transaction data set by the month. (You’ll have to reset the filter to include the helper column.) Specifically, display only the records for August (8) and compare the SUBTOTAL() results to grand total result in the summarizing matrix. As you can see in Figure G, the results match.

Figure G

Compare the transaction and matrix functions for August.

Next, filter the transaction data set by choosing Jojo’s Car Wash for the supplier. Don’t clear the month filter; we want the August total for Jojo’s. As you can see in Figure H, the results still match–Jojo’s total for August is $333.

Figure H

Check the matrix values against the filtered transactions.

The filter isn’t necessary, but it visually confirms that the matrix functions are correct. On the other hand, if you don’t need a summarizing display, you can skip the matrix and use simple filtering. Your solution depends on how you use the information. Kay didn’t ask for a filtering solution, but you might be able to use it.

Absolute references for a Table

Jan loves Excel’s new Table object (so do I) but ran into a problem when trying to copy a formula that contained Table references. If you’re not familiar with this syntax, read Use Excel’s table nomenclature to quickly create dynamic formulas.

Referencing a Table object’s columns is easy–use the column’s name. But things get more complex when you want to copy a formula without updating the column references. The column reference is relative, not absolute, and adding the $ sign won’t work with this structure.

We can illustrate this problem using the transactions data set from the earlier example. If you enter the following function into J4 and copy it, the column references update as relative references:

=SUMIFS(Table4[Total],Table4[Supplier],$H4,Table4[Month],J$3)

This function copies to K4 as

=SUMIFS(Table4[Method of Payment],Table4[Total],$H4,Table4[Date],K$3)

and L4 as

=SUMIFS(Table4[Month],Table4[Method of Payment],$H4,Table4[Supplier],L$3)

You can’t press [F4] or manually enter the $ sign to make the column references absolute. It just won’t work. Instead, to make a Table reference absolute, use the following syntax:

tablename[[columnname]:[columnname]]

To make a row absolute, use the @ character in the following form:

tablename[@[columnname]:[columnname]]

To apply this absolute reference syntax to our Table matrix, enter the following function into cell J4 (Figure I) and copy it to the remaining matrix:

=SUMIFS(Table4[[Total]:[Total]],Table4[[Supplier]:[Supplier]],$H4,Table4[[Month]:[Month]],J$3)

Figure I

We’ve applied the absolute reference to our Table matrix.

The references remain absolute and the matrix returns the expected results. If you don’t use the appropriate Table references in your matrix functions, they won’t update when you add new records to the Table.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read…

Use PowerPoint’s push transition to connect related slides

Master sorting basics in Excel

Use a custom style to annotate a Word table of contents

Office Q&A: A confusing Word control and a simple Excel conditional formatting rule