Summary of data on multiple criteria on multiple worksheets

Liam Bastick has modeling financial services and training customers for more than two decades available. A professional mathematician and accountant worked in many countries with many internationally recognized customers, provision and review of the strategic and operational models for various important business obligations. You can check out Liam's previous articles at www.sumproduct.com/thought, where you can subscribe to the monthly tips and tricks newsletter.

Ever to sum of data based on multiple criteria, in different Microsoft Excel worksheets? This article provides a quick tour of indirect references and table functionality during combination properties of the function SUMPRODUCT SUMIFS function provides a solution for the mother of all multiple criteria problems.

The functionality is best to walk through an example explained:

Ivana car sales has four business areas, smart as North, South, East and West. Each quarter the four divisions will submit sales reports detailing of the month sales, salespeople, the Auto color and the price for the car was sold.

The question is: How can you determine how many red cars Charlie in total sold in February in all four divisions?

The answer would be relatively easy if the data on a worksheet.  To cope with a single criterion of SUMIF would admirably well while SUMPRODUCT for multiple criteria can be used to generate the response (for more information see my blog posts to SUMPRODUCT function and approaches to the management of multiple criteria in a worksheet).

Because the reports are furnished, there are a few considerations:

It is important that these reports are from a master template, i.e., each report has the various fields in the corresponding column. This is important for what follows. If this is not the case, you should modify the data sheets. If the four reports do not in the same workbook are saved, the data on separate worksheets within the workbook should be imported, where the expense report to be generated.

If we want to refer to multiple data sheets, we need to know the names of these worksheets. For maximum flexibility, I would suggest, store them in a table (to highlight the data and on the tab of the Ribbon command bar, select Insert , table). Tables, see my blog for a full explanation.

I named this table Division_Table , as the divisions listing for analysis. To show why I used a table, keep in mind that I have recorded Division not the West. So I would give these to the line under East, West would be part of the table. This is a very useful function to refer to the lists.

It is also important to note that named the three divisions (South and East here North,) have identical names on the tab sheet name - otherwise, this solution will not work. Make sure that the text is exactly the same with that in the page tab.

We are now ready. In a separate worksheet, I would create the following table:

The formula in cell I12 here is probably one of the easiest that you have ever met (that is, if you are working in the world of relativistic quantum mechanics accidentally!):

= IFERROR (SUMPRODUCT (SUMIFS (indirect ("'" & Division_Table [relevant departments] & "'!"))))I: I "), indirect ("'"& Division_Table [relevant departments] &"'! ")"("F: F"),$ F12, indirect ("'" & Division_Table [relevant departments] & "'!")("G: G"),$ G12, indirect ("'" & Division_Table [relevant departments] & "'!")"H: H"),$ H12)),)

You know that you have created a monster, if you have three complex Excel functions in a fourth boxes. But have no fear. To work out what's going on, explains from the inside out is (as this is the order in which Excel calculates this formula):

Indirect (detailed here)--this function returns an array of reference such as ' North ' column F, 'South' column F, etc. that can be used by other functions. Note that "'" is the formula, quotation mark, apostrophe (the syntax in General to the sheet names), then quotation marks.SUMIFS-diese function now applies several criteria for the analysis of summation. However, when used alone, this only reports created in the first worksheet (the order of presentation within the workbook is irrelevant). Here comes the next function...SUMPRODUCT-diese function is necessary, as it is often referred to as a "pseudo array"function. What does this mean in practice here is that there will be conducted the SUMIFS function between all three worksheets. IFERROR--diese error trap ensures that if a worksheet in the Division_Table listed does not exist or there is a blank line, the formula will produce an error - #REF!, for example.

This is it. Now you will the workbooks to sum up and reports that Charlie was selling red car capable of, the data about $43 k in the four business units in February.

There are to consider two other possible solutions: pivot tables by using data from multiple worksheets or create a master data sheet as an intermediate step where all data on a worksheet is registered. I produced this response, because it was true to the particular circumstances of the problem.

Final thought is: keep it simple (also known as the KISS-rule). When data on multiple worksheets can complicate the problem and complexity. Before writing a wide range formulas as above described, you should always simplify the model structure first.

-Liam Bastick

View the original article here