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.

  1. =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.

  2. =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

  3. =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.

Share on Google Plus

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.
    Blogger Comment
    Facebook Comment

1 comments:

  1. Alternatively SUMPRODUCT could be used to achieve the same result

    The format I usually use is =SUMPRODUCT(--(condition1)*(condition2),range to be summarised)

    so in the above example it would be
    =SUMPRODUCT(--($A$2:$A$29=$E6)*($B$2:$B$29=F$5),$C$2:$C$29)

    a few notes

    "--" before condition1 forces the calculation

    if the conditions are an either or then use "+" rather than "*"(which translates as and) between conditions


    The conditions when calculated become arrays of 1s and 0s (true and false)

    good luck with the blog

    ReplyDelete

Wednesday, July 1, 2009

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.

  1. =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.

  2. =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

  3. =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.

How to calculate subtotals using Array functionsSocialTwist Tell-a-Friend

1 comments:

Higgs July 10, 2009 at 6:21 PM  

Alternatively SUMPRODUCT could be used to achieve the same result

The format I usually use is =SUMPRODUCT(--(condition1)*(condition2),range to be summarised)

so in the above example it would be
=SUMPRODUCT(--($A$2:$A$29=$E6)*($B$2:$B$29=F$5),$C$2:$C$29)

a few notes

"--" before condition1 forces the calculation

if the conditions are an either or then use "+" rather than "*"(which translates as and) between conditions


The conditions when calculated become arrays of 1s and 0s (true and false)

good luck with the blog

Post a Comment