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

Result! Merging data from multiple worksheets

The Decatur Golden Gators football team is the pitch (a field you uninitiated people) must take in order to perfect their skills and new competitors to beat. The coach is always dizzy, and, to be honest, fiscally concerned. Buy snake, mini-vans, snacks, replace broken merchandise and purchase Beiber ticket is not cheap!

Manage all these issues and they roll up into a spreadsheet is pushing the limits of his knowledge of Excel. He has various worksheets for transport, tasty treats, website-hosting - he cuts and adds numbers of anyone in his household. Cntl + C inevitably leads to errors.

The Excel consolidate feature provides an easy way of the coach his data from different worksheets merge into a main worksheet, a more complete picture of his expenses allowed him (in fact, he can merge up to 256 worksheets!). With the consolidation function, the beleaguered coach get handle to motivate team expenses and focus more on the team.

The steps to merge or consolidate, data are pretty simple. Here is how to do it.

Use our sample workbook or if you learn through their own workbook, you need to do the following:

The workbook contains multiple worksheets the workbook (or else there is not much point using the consolidate function) a home should contain worksheet, for the consolidation of the data, analysis are most important worksheet that you must use for the column and row headers the same name

1. Create a home worksheet worksheet in your workbook that uses the same column and header name as the other worksheets. In this scenario, we call the home total.

(2) Select the cells in which you want to merge data from your other worksheets, on the worksheet effort .

3. Click the tab, click data , and then click the consolidate button in the Data tools .

,

4. Consolidate dialog box, click the highlighted button next to the field reference .

5. Click on the transport - worksheet-tab, to view it.

6. In the worksheet, select the transport all data you in total filename_ want to merge. In this exercise, simply select the data costs .

7. Click on the right side of the field consolidate - reference to return to consolidate dialog box.

8. The consolidate dialog Add , click the button to add the transport data, you just selected. This step ensures that the transport data, you just selected in the total expenses worksheet are merged.

 

9. Repeat steps 4 through 8 described to consolidate the other worksheets in total filename_.

10 Once you have added all the worksheets that you want to merge into the total filename_, click OK in the dialog box for the consolidate button .

The hapless coaches followed the steps above, and now its total expenditure worksheet looks like this:

Better budgeting means more Beiber!

View the original article here