Microsoft

Office Q&A: Troubleshooting Excel functions and getting unexpected results

Susan Sales Harkins helps a reader troubleshoot a SUMIFS() function, and they both get a bit more than they anticipated.

Susan Harkins Office Q&A

Sometimes, a function doesn't return the expected results -- and the more you troubleshoot, the more confused you get. It certainly happens to me! Tony ran into this with a SUMIFS() function, but it wasn't actual the SUMIFS(). It was the WEEKNUM()'s function -- or was it? As it turns out, there's more than one problem to consider, and each potential solution exposes another confusing behavior.

Tony wants to sum values for records with a specific year and week number value. Excel's SUMIFS() function is the perfect function for the job, right? Unfortunately, his solution didn't work as expected, and Tony narrowed down his problem to WEEKNUM()'s inability to recognize the column reference format (WEEKNUM()doesn't support arrays). There actually was more going on, but Tony didn't realize it at the time.

Problem #1

Figure A shows a simple representation of Tony's situation. The function

=SUMIFS(B:B,A:A,YEAR(D2),A:A,WEEKNUM(D2))

returns 0 instead of an error value. If you're dealing with lots of data, you might not realize that the function isn't returning the results you expected -- that's dangerous! This function doesn't actually match the criteria value and the criteria range values; it'll never return anything but 0 because the two types don't match. You're trying to match years to full dates.

Figure A

Figure A

SUMIFS() doesn't return an error value.

Problem #2

You might consider inserting YEAR() and WEEKNUM() functions, as follows:

=SUMIFS(B:B,Year(A:A),Year(F2),WEEKNUM(A:A),WEEKNUM(F2))

That's what Tony tried to do, but Excel doesn't like that effort either, even though the types match. The criteria range arguments must be true ranges, so you can't combine the functions with the range references.

Problem #3

The column references seem to be a good solution if you're adding to the data range, but the next step makes it clear that it just won't work. As you can see in Figure B, helper columns using column references won't get the job done either. It seems a bit confusing, because the YEAR() function in column C uses a column reference -- YEAR(C:C) -- and it works fine. I don't have an explanation for why WEEKNUM() can't evaluate a column reference, but it's something we have to accommodate.

Figure B

Figure B

WEEKNUM() doesn't evaluate the column reference.

Solution #1

SUMIFS() will work, but you'll want to reference helper columns instead of the original date values. Figure C shows this configuration; the SUMIFS() in F3 evaluates as expected:

=SUMIFS(B:B,C:C,YEAR(F2),D:D,WEEKNUM(F2))

The function refers to the helper columns C and D. In addition, the YEAR() and WEEKNUM() functions parse only the components needed from the filter date in F2. That way, the values in the new criteria ranges and the actual criteria values match types.

Figure C

Figure C

Helper columns make quick work of the criteria values.

We've fixed all three problems:

  • The criteria value and criteria range value types match.
  • The criteria range arguments are true ranges.
  • WEEKNUM() is correctly evaluating its range argument.

There are other routes to the same result, but working through the different components helps expose something that might come in handy in other solutions: WEEKNUM() won't evaluate a column reference. It was only part of Tony's problem though.

Worth noting

It's worth noting that WEEKNUM() has an optional argument that lets you alter the beginning day of the week. The default argument value is 1 and the week begins on Sunday. If you use the value 2, the function will consider Monday the first day of the week.

Solution #2

If you need to filter using an actual year and week number value instead of a date, you can adjust the filtering range, accordingly. Figure D shows the same result as the first solution, except we've changed the filtering values referenced by the following SUMIFS() function:

=SUMIFS(B:B,C:C,F2,D:D,F4)

This solution is less complicated because we've omitted the YEAR() and WEEKNUM() functions entirely. Neither solution is superior -- the solution you implement depends on your needs.

Figure D

Figure D

This SUMIFS() references two different criteria values.

Solution #3 (.xlsx format only)

In truth, the column reference isn't entirely necessary, because Excel automatically extends the formulas and updates the SUMIFS() references. Figure E shows the result of adding a record after changing the SUMIFS() function references to ranges as follows:

=SUMIFS(B2:B6,C2:C6,YEAR(F2),D2:D6,WEEKNUM(F2))

As you can see, Excel automatically extended the formulas in columns C and D but didn't update the SUMIFS() in F3 to reflect the new record.

Figure E

Figure E

By default, Excel extends formulas (and formatting).

If you want to retain the range references, you can convert the data range to a table -- then Excel will update the SUMIFS() references too. To convert the range, click anywhere inside the data range and do the following:

  1. Click the Insert tab.
  2. Click Table inside the Tables group.
  3. In the resulting dialog, click OK. If your data range does not contain header cells, be sure to uncheck that option.

When you add a new record to a table object (a feature that showed up in Excel 2007), Excel updates the SUMIFS() references, as shown in Figure F.

Figure F

Figure F

Use a table to update dependent functions and formulas when adding new records.

Back it up a step

Before we converted the data range to a table, I mentioned that Excel will extend formulas by default when adding new records. If your sheet isn't updating, someone has disabled this default setting. To reset it, do the following:

  1. Click the File tab. In Excel 2007, click the Office button. If you're using Excel 2003, choose Options from the Tools menu, and skip to step 3.
  2. Choose Options (under Help). In Excel 2007, click Excel Options.
  3. Select Advanced in the left pane. In Excel 2003, click the Edit tab.
  4. In the Editing Options section, check the Extend Range Formats and Formulas option.
  5. Click OK to return to the sheet.

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 instance, "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. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
hycm53
hycm53

My solution for this problem would be:

1. Delete column C: column C doesn't make sense, and waste space;

2. Don't need insert table. Only change formula to :=SUMIFS(B:B,C:C,WEEKNUM(E2)). Excel will update until doesn't match criteria.

Editor's Picks