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)
Search JabSto ::




Custom Search