Excel serves up another hidden surprise — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Excel serves up another hidden surprise

Get PDF file

by on
in Excel Training,Office Technology

Under the heading of “Great Functions I Never Knew,” is the AGGREGATE function. While it’s use­ful at times that totals added to a Table Style formatted range always re­flect the filtering (total reflects visible rows), sometimes you also need the total that includes all rows.

The AGGRE­GATE function lets you specify a code for the function, such as 9 for SUM, and a code for what values the function should ignore (4 ignores nothing). Then, specify the range. So for the table range called Table1[Qtr], the formula would be=AGGREGATE(9,4,Table1[Qtr 1]).

Leave a Comment

Previous post:

Next post: