The Excel Pages

(Adapted from http://www.excel-vba.com)

Rating of the paragraphs below:

 Indispensable        Very useful        Interesting

SumProduct Formulas and Array Formulas

Here is a table to illustrate the power of SumProduct formulas and Array formulas which are in fact "queries in a cell".

We need a formula that can sum a column (quantities or amount) based on the value of one or more criteria in other columns (date, product, cities, departments, countries, sales rep., etc.).

Imagine 100 products, 20 cities, 12 months, 5,000 records...
THERE IS NO OTHER WAY THAN SUMPRODUCT FORMULAS OR ARRAY FORMULAS TO ANSWER TO THE FOLLOWING QUESTIONS:

January Sales : $5642

Sales of Shirts for Chicago : $9642

Total Sales of Shirts and Pants : $20575

You must enter Array formulas by clicking simultaneously "Shift/Ctrl/Return". If you have done it right, "{ }" will appear at each extremity of the formula.

SumProduct formulas and Array formulas don't have to be on the worksheet where your data sit. They can be on another worksheet and even in another workbook.

SumProduct formulas and Array formulas don't work if all the columns or lines that you refer to don't have the same number of cells.
For example, the following formula would not work: SumProduct((A1:A6="John")*(A1:A4)) because of (A4,A6) and this is true with named fields too.

Do not includes your title cells in your selected cells or named fields, SumProduct formulas or Array formulas do not SUM text cells.

Once you have created a data sheet, you may want to change the data. Do not use the "Delete" button, you will destroy your formulas. Instead, select the data, go to the menu bar and use "Clear/Contents". You can then insert new data in your table and the formulas are still valid.

Remember that the size of a range that you use in SumProduct or Array formulas can be longer than the number of data that you have.

Hence, sum row 2 to row 500 even if you have 50 rows of data, if you add data after row 50, your SumProduct formulas and Array formulas will pick them.

Index/Match Formulas

When the data that are sent to you are summarized, you will use Index/Match formulas to create reports from the database. If they are not summarized, you will have to use array formulas.
Here are formulas combining INDEX and MATCH functions.

The INDEX function allows you to retrieve a value from a database when the column and row numbers are known.

Percentage of people using the train in Chicago = 5%

=INDEX(A1:E5,3,3)

But if every time the data are imported, there are new cities and means of transportation and the order of the elements is different, you will need a function that looks for a value and returns a column or row number: the MATCH function.

The line number for Chicago = 3

=MATCH("Chicago",A1:A5,0)

The column number for Train = 3

=MATCH("Train",A1:D1,0)

Combining these three formulas will return 5% whatever the position of Chicago and Train in their respective range:

=INDEX(A1:E5,MATCH("Chicago",A1:A5,0),MATCH("Train",A1:D1,0))

Using the ISERROR function to avoid the #NA, you will have an industrial strength tool to extract information from a table or a database.

Reporting with Excel

Either you import your data using one of the 5 methods presented previously or you enter your data manually creating an Excel database. See the page on database to know more about the subject

Before you move on, the following notes might become useful.

1°)    Always import data or enter your raw data on a separate sheet that you could call "Data01".

2°)    Develop different reports on different sheets, develop summaries on a separate sheet, use hyperlinks to navigate between sheets.

3°)    If the summaries or reports that you develop contain data that will be used in another report, you can either build them so that they are convivial to your reader and at the same time so that data are easily retrievable with Index/match formulas or array formulas. If you can't reach a compromise between your two readers (humans and computer), create a "DataOut" sheet where you retrieve and organize the data from the report using simple "=" formulas.

4°)    When you work with long formulas, you might want to copy/paste part of some formulas. Go to the formula window, highlight the part of the formula that you want to copy, CLICK ENTER and then go to the destination cell to finish the transaction.

5°)    Use hyperlinks to navigate between summary and details of complex reports.

Relative and Absolute References

Try to develop a single formula that you will copy/paste in an entire report (It is the way I usually do it). To do so, you have to become good at using relative or absolute references. Sometimes you need a relative reference (B4) sometimes you need an absolute reference (B$4$) and sometimes you need something in between (B$4, B4$). Click on the reference in the formula bar and use the "F4" key once, twice or three times as you need.

Named Fields

You need to name the fields and cells that you use within your VBA procedures. The easy way to name a range is to select it then move the cursor to the Name Zone. You enter the name and you click "Enter".
To rename a named field, go to "Insert/Name/Define", select the named field, make the changes and, click "Add/OK"

To resize a named field, go to "Insert/Name/Define", select the named field, make the changes and, click "Add/OK"