Perform complex data analysis without array formulas

 

by Kara Soos

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

 

While you routinely need to extract specific data from your Excel worksheets, a simple list of grand totals often isn’t enough to satisfy decision-makers who are trying to take your company in the right direction. Fortunately, you can make custom calculations without the trouble of wrapping your head around complex array formulas — you just need the SUMPRODUCT function.

 

To get a detailed analysis of our data using the SUMPRODUCT function, we’ll:

• Create named ranges so it’s easier to build our formulas.

• List how many products sold at each store have a retail price of 50 dollars or more.

• Adjust our SUMPRODUCT formula so it sums the total sales for products priced over 50 dollars instead of simply counting the number of products that fall within the price range.

• Combine two SUMPRODUCT formulas to find the average number of products priced over 50 dollars sold at each store.

 

When you need to present a thorough data analysis to your colleagues or supervisor, the analysis is rarely an overview. You often need to make calculations that match very specific criteria. For example, you might need to find the average number of products priced within a particular price range sold at a specific location. One simple SUM or AVERAGE function just won’t cut it in these situations — and array formulas are confusing and time-consuming. Instead, try out the SUMPRODUCT function.

 

Check out how it works

To demonstrate the different kinds of data analysis you can perform with SUMPRODUCT’s help, we’ll use a data table that lists the monthly data for three store locations including the number of products sold in each location, the retail price of those products, and the total monthly sales per product for each store, as shown in Figure A.

 

Download: You can follow along with our example by downloading and extracting the file sumproduct.xls from the URL given at the beginning of this article.

 

A:

We’ll pull out specific information from this source data without using array formulas.

 

Streamline your formulas from the start

Before you even start building formulas, it’s easier if you create named ranges for the data you’re working with. Not only does this help you build formulas without making data range errors, but it makes it easy to learn how to build complex formulas. For our example, we’ll create the named ranges listed in Table A.

 

To quickly create a named range:

1. Select the data range you want to include.

2.  Click in the Name Box and enter the name you want to assign. (You cannot includ