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

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: