How to calculate subtotals using Array functions

I have discussed basic concept of array functions, its advantages and disadvantages in my previous post. Now, let us now talk about an example where we generally get to use Array Functions.

Example:
In our example, we have some data related to sales of 3 products - Product A, Product B and Product C. The sales force is active in all 4 geographic regions - East, West, North and South. Now we need create a small report (just 4 cells of data) that will summarize the whole data. This report will have subtotals by Region and Product. Follow the below steps for this:

Create an Excel spreadsheet with some sample data (alternatively you can download this spreadsheet) which has these columns - Region in column A, Product in column B and Sales in column C. Also, fill some sample data for these 3 columns
Create a small matrix table with columns titled as each product and rows titled as each region. The matrix should look something like this:

Let us now try to formulate one single formula for subtotals that can be used with all the cells in the matrix. Here is the formula that I used for calculating the totals for each cell in the above matrix:

=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))

..and i entered this function as an array function, i.e. using CTRL+Shift+Enter

Let us understand what does each part of the formula does.

=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))

This portion of function gets executed first. This part checks if the Product column has 'Product A' in it. If yes, it lets the first cell go for next level of validation (next step of calculation), If the cell does not have 'Product A', then 0 value is passed on to next level
Note: I hope you remember I discussed in my previous post that array functions process each cell at a time. To refresh your concept read more about Array Functions here and here.
=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))
Similar to previous part of the formula, this part check the cell for Region. If the cell has region as 'East'. If true, then the cell is passed on to next level of formula, i.e. Sum() function. If the cell does not have 'East', then 0 value is passed on to next level
=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))

Here, the value passed from previous 2 steps gets accumulated and is added up. (Learn more about Sum() Function and other Math Functions)

Now, you copy and paste this formula to other cells of the matrix and you will get the required results.

Note: The formula will take required cells from the column and row heads for validation by itself. This has been done through using 'Relative Reference' technique of writing formula in Excel. I will take this up in my later posts.

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.

Wednesday, July 1, 2009