**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.

Alternatively SUMPRODUCT could be used to achieve the same result

ReplyDeleteThe 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