123

The formulas should read (A2:D2) instead of (A2:C2). Sorry for the confusion, but an edit function would be nice. :)

Regarding pivot tables, I recommend that you always select empty cells to show zeros. The following example will illustrate why:

First, consider the case where empty cells are left blank.

(A) (B) (C) (D)
(1) Jan Feb Mar Apr
(2) 24 (blank) (blank) 24

Using this pivot table, the formula =average(A2:C2) would return the value "24." It ignores the blank cells.

Now, let's contrast that with the case where empty cells contain zeros.

(A) (B) (C) (D)
(1) Jan Feb Mar Apr
(2) 24 0 0 24

Now, the formula =average(A2:C2) would return the value "12."

If the numbers above represent revenue $, for example, the lack of revenues in February and March should not be overlooked in calculations. Thus, I would expect calculations built on the latter settings to be the correct ones for most users.

Finally, this issue is not exclusive to the =average formula. Others, such as =min and =mode, may also be thrown off by blank cells in a pivot table.

There are a few people who know about the function Subtotal(Function_num,Range), but even only a few of these know about how powerful it can be when adding columns in a table in conjunction with Data\Filter\Autofilter.

This is a tip worth sharing.

Multiple Sumif formula
For multiple sum if, first you need to open brackets as follows: {=sum(if(....="x",if(...="y"..,range of values,0),0))}. Second, you need to type {} at the beginning and at the end of the formula by pressing Ctrl, Upper Case, Enter keys at the same time while the cursor in on the formula.

When creating a row index it is better to use the formula =row(). Then when a row is inserted, one does not have to recopy the formula A1+1, for example, for the entire list. The formula has to only be added to the inserted row.

Here is an oldie but a goodie that helps when you are working in a heavy-formulated Excel file. To review and understand the formulas without going into each cell, hit ALT and the (~). This will display all live formulas in the sheet. Hit the key combination again to return to the number value.

When creating a lookup table with VLOOKUP, index your rows - but don't use hardcoded sequential numbers (e.g. 1, 2, 3, 4, etc.), because this will cause problems if you later insert a row in the middle of the data set. Instead, put a "1" in the first row and then use the formula A1 + 1 and then copy that down the column so that the indexing is dynamic and automatically incorporates any added rows.

This is a short-lived tip for those who run into backward compatability problems with Office (as I do when trying to open spreadsheets from work on my older home computer). If you can't open a .xlsx file, one way to view and even work with the contents is to open it in Google spreadsheets. I would only do this for viewing and limited operations, though, not for making major changes. Google spreadsheets are still somewhat buggy.