Microsoft Office Tutorials and References

In Depth Information

result to ignore hidden rows. To sum the Sales column and ignore filtered rows, use

either of the following formulas:

=SUBTOTAL(109,Table2[Sales])

=AGGREGATE(9,1,Table2[Sales])

Excel provides some helpful assistance when you create a formula that refers to data within a table. Figure 9-15

shows the Formula AutoComplete feature helping create a formula by showing a list of the elements in the

table. The list appeared after I typed the left square bracket.

Here's another example that returns the sum of the January sales:

=SUMIF(Table2[Month],”Jan”,Table2[Sales])

For an explanation of the SUMIF worksheet function, refer to Chapter 7.

Using this structured table syntax is optional — you can use actual range references if you like. For example,

the following formula returns the same result as the preceding one:

=SUMIF(B3:B8,”Jan”,D3:D8)