MacLochlainns Weblog

Debbie asked a question about why a comparison wasn’t working in the Microsoft Excel SUMIF function when the initial array value was empty. This post answers that question.

The SUMIF function takes a lookup range, which anticipates a one column with a lookup value and another column with a value. The SUMIFS function works along the same line but accepts multiple lookups against a series of lookup list columns.

This shows a simple model of the SUMIF function:

The formula in cell G3 is:

=SUMIF(B$3:B$5,"<"&$E$3,C$3:C$5)

If you left off the $ symbol and made the comparison "<"&E3, you could add the sums for any number of values that are less than a value in the same row (or counting index with named ranges). You could also make the inequality symbol a parameter with this modified formula:

=SUMIF(B$3:B$5,$E$5&$E$3,C$3:C$5)

Now, your analysis model lets you choose whether you want to look for the sum greater than or less than a specific number in the lookup range. Named ranges compare index values between the lookup range and comparison range values, which allows them to be in different rows or spreadsheets. The only problem is that the label has an inverted inequality sign. You can fix that by constantly editing the description or by simply replace the text of the label with a dynamic text string, like this:

=CONCATENATE("Comparison """,$E$5,""" Value")

After applying the fix to the label, it looks like:

When the lookup column contains all nulls, the same formula returns a zero, as shown in the illustration.

You'll also get an error flag, unless you unchecked the Flag formulas that refer to empty cells in the error checking options. The IFERROR function can't be used to suppress this type of error.

Why doesn't the error return a null value? That's because an equality operator compares values in the array against a static or dynamic lookup value. While equality operators don't implicit cast values for comparison, they also don't compare null values. Only the ISBLANK function lets you find an empty cell and it can't be used inside a SUMIF function call.

You must install and then configure a Windows Data Source Name (DSN) for SQL Server’s ODBC before you can connect MySQL Workbench to a SQL Server and migrate data. If you fail to set it up, you can’t complete the first step of the MySQL Workbench migration wizard, as shown in the image to the right.

For MySQL readers, this was posted as part of a replicateable test case for Alfredo’s MySQL Workbench team. A Windows OS version of Bug 66516.

You configure a Windows Data Source Name (DSN) for Microsoft SQL Server 2012 after a successful installation (shown in this related blog post), by performing the following steps.

1. You need to create a data source in the operating system. You should open the Control Panel and click on the Administrative Tools menu item to begin the installation of a new data source.

2. Click the Data Sources (ODBC) menu item to start the process.

3. This is where you add a new User Data Source. Click the Add button on the right of the dialog box.

4. The Create New Data Source dialog box should show the SQL Server Native Client 11.0 that was installed when you installed the database. Click on it in the selection box, and then click the Finish button.

5. The first dialog of the Create a New Data Source to SQL Server process presents the following dialog, which prompts you for a Data Source Name (DSN), a description, a target server. I’ve entered SQL Server ODBC as the DSN and description, and chosen the local database server. The local database server is the server running on the localhost machine. Click the Next button to continue the process.

6. The second dialog of the Create a New Data Source to SQL Server process prompts with the following dialog, which lets you opt for integrated Windows authentication or user credential authentication. The easiest set up when you’re running a test case on a single instance selects integrated windows authentication. Click the Next button to continue the process.

7. The third dialog of the Create a New Data Source to SQL Server process prompts with the following dialog, which lets you change the default database, attach a database filename, set ANSI rules, the application intent, and multi-subnet failover. The default options are selected in the dialog. Click the Next button to continue the process.

8. The fourth dialog of the Create a New Data Source to SQL Server process prompts with the following dialog, which lets you change the language, encryption, character translation, regional settings, and long query execution. The default options are selected in the dialog. Click the Next button to continue the process.

9. The next dialog displays the settings for the ODBC SQL Server Setup. You should always confirm that things work by clicking the Test Data Source button.

10. The next dialog displays success when you made working choices and failure when something is misconfigured. Click the OK button to continue the process.

11. The OK button concludes the Create a New Data Source to SQL Server process, and returns you to the list of valid ODBC Data Sources. Click the OK button to conclude the process.

Hopefully, this helps those learning how to set up Microsoft SQL Server to work with MySQL Workbench’s migration tool.

Somebody asked how to solve a problem with the =AVERAGEIFS function and non-contiguous ranges. The solution requires a combination that uses a group of =SUMIFS function divided by an equivalent group of =COUNTIFS functions.

The following illustration shows the problem. It has groups of quarters, and within the quarter groups rows hold products and columns hold weeks. Unfortunately, the =AVERAGEIFS function disallows non-continguous source ranges. It does support multiple criteria, which may be non-contiguous ranges provided they match the number of elements in the source range.

While the sums can be calculated by adding the Total column, the average can’t. You would use the following to calculate the actual average:

=IFERROR(((SUMIFS(D5:P5,D5:P5,">0")+SUMIFS(D18:P18,D18:P18,">0")+SUMIFS(D31:P31,D31:P31,">0")+SUMIFS(D44:P44,D44:P44,">0"))/(COUNTIFS(D5:P5,">0") + COUNTIFS(D18:P18,">0") + COUNTIFS(D31:P31,">0") + COUNTIFS(D44:P44,">0"))),0)

The resolution adds a set of range sums and divides it by an equivalent set of range counts where both values are greater than zero. An =IFERROR function substitutes a zero in the event that the denominator is zero. This last step ensures that the an empty set of cells won’t raise an exception.

I know it helps the person who asks but hope it helps others too.