Ravindra BagaleCourses & study guides Track your progress

Excel AGGREGATE Function

By · Last updated:

Chala mitrano — today AGGREGATE. SUBTOTAL’s big cousin — ignore errors and hidden rows on purpose.

Fictional sample data for learning. Error-tolerant totals in Gurgaon MIS and London finance ops. Mixed ₹ and £.

Purpose

Like a more flexible SUBTOTAL — pick a function and options to ignore errors, hidden rows, or nested SUBTOTAL/AGGREGATE.

Return value

The aggregate result.

Syntax

AGGREGATE(function_num, options, ref1, [ref2], …)

Arguments

ArgumentRequired?Description
function_numRequiredWhat to compute (1 AVERAGE, 2 COUNT, 9 SUM, 14 LARGE, …).
optionsRequiredWhat to ignore: e.g. 6 = ignore error values; 5 = ignore hidden rows; 7 = ignore hidden + errors.
ref1RequiredThe main range (or array for some functions).
ref2,…OptionalExtra args (e.g. k for LARGE).

Version

Excel 2010+. LibreOffice: limited / may differ — SUBTOTAL is the safer LO fallback.

How to use — Why → What → How

Why: Real sheets have #N/A in lookup columns. AGGREGATE can sum the clean cells without cleaning every error first. Error-tolerant totals in Gurgaon MIS and London finance ops. Mixed ₹ and £. In Indian back offices and foreign sister teams, AGGREGATE shows up on totals, rates and pack sheets every week.

What: AGGREGATE runs function_num with behaviour set by options. Common: options 6 ignore errors; 7 ignore hidden rows and errors. Read every argument in the table once before you type; each one changes the answer on purpose.

How — numbered steps:

  1. Type =AGGREGATE(9,6,B2:B20) to SUM while ignoring errors.
  2. Use 14 with k for LARGE.
  3. Fall back to SUBTOTAL in LibreOffice if needed.

Ravindra Bagale's Tip

Friends, after AGGREGATE, find one column or range in a real workbook this week where this lesson saves time.

Ravindra Bagale's Tip

Friends, sleep on AGGREGATE; rebuild example 1 tomorrow without looking.

Ravindra Bagale's Tip

Friends, say the business question in plain words before =AGGREGATE.

Ravindra Bagale's Tip

Friends, check a known case with a calculator before you trust a long AGGREGATE chain.

Ravindra Bagale's Tip

Friends, after you finish AGGREGATE, open yesterday’s real workbook and find one place this function would have saved ten minutes.

Ravindra Bagale's Tip

Friends, treat AGGREGATE like a seatbelt on number columns — check a known case first.

Download practice file: practice-aggregate.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) SUM ignore errors

Gurgaon.

₹
10
#N/A
30
Formula: =AGGREGATE(9,6,A2:A4)

Result: 40

What it means: 10+30; error skipped (options 6).

2) AVERAGE ignore errors

Mean clean.

₹
10
#DIV/0!
30
Formula: =AGGREGATE(1,6,A2:A4)

Result: 20

What it means: (10+30)/2.

3) London £ LARGE

2nd largest.

£
5
15
25
10
Formula: =AGGREGATE(14,6,A2:A5,2)

Result: 15

What it means: function 14 = LARGE; k=2.

4) Plain SUM no errors

Happy path.

₹
10
20
30
Formula: =AGGREGATE(9,4,A2:A4)

Result: 60

What it means: Options 4 = ignore nothing special; total 60.

5) COUNT numbers

function 2.

Val
10
x
30
Formula: =AGGREGATE(2,6,A2:A4)

Result: 2

What it means: Two numbers.

6) vs plain SUM

Errors.

₹
10
#N/A
30
Formula: =SUM(A2:A4)

Result: #N/A

What it means: Plain SUM fails; AGGREGATE(9,6,…) succeeds.

About this data. Small demo rows for learning. Real sheets are longer; the same formula pattern scales when columns are clean.

Common errors and fixes

#VALUE! — Bad function_num / options. Fix: Check Excel’s lists.
LO missing — Older LibreOffice. Fix: Use SUBTOTAL / IFERROR helpers.
Wrong options — Still seeing errors. Fix: Try options 6 or 7.

Notes and tips

  • Revisit the AGGREGATE errors table after the quiz.
  • Build example 1 from memory on a blank sheet.
  • Check number vs text if a result looks wrong.
  • Teach one tip in Marathi or Hindi this week.
  • Keep currency symbols consistent inside one report column (practice mixes ₹ $ £ €).
  • Drill: SUM that ignores #N/A with AGGREGATE from memory.
  • AGGREGATE vs SUBTOTAL: richer options and error handling versus classic filter totals.
  • Fictional sample data only — names and amounts are for learning.
  • Print the AGGREGATE revision card for one week of real worksheets.
  • Ask juniors to predict before they press Enter.
  • Track your top mistake with this function monthly.
  • Error-tolerant totals in Gurgaon MIS and London finance ops. Mixed ₹ and £.
  • Interview tip: explain the business question before the formula.
  • Classroom drill: invent three brand-new formulas from your city without the page open.
  • If a result looks wrong, check blanks vs zeros before rewriting.
  • When Excel versions differ, note the LibreOffice / Sheets fallback in a cell comment.
  • Prefer clear ranges over whole-column refs on huge sheets if speed matters.
  • Document units (₹, kg, days) in the header so totals stay honest.
  • Print the revision card for AGGREGATE for one week of real worksheets.
  • When helping a junior, ask them to predict before Enter.
  • Share one tip with a classmate in Marathi or Hindi.
  • Keep currency symbols consistent inside a single report column.
  • Interview tip: explain the business question before the formula.
  • If a result looks wrong, check blanks vs zeros before rewriting maths.
  • When Excel versions differ, write a LibreOffice fallback under a comment.

In real work

Drill: SUM that ignores #N/A with AGGREGATE from memory. Error-tolerant totals in Gurgaon MIS and London finance ops. Mixed ₹ and £. Classroom drill: write three brand-new AGGREGATE formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose and watch the error; recovering from that break is half of interview readiness. Always verify with a known case before trusting a long chain. Keep a personal notebook of the one mistake you make most often with this function; review it monthly.

How it compares

AGGREGATE vs SUBTOTAL: richer options and error handling versus classic filter totals.

Interview corner

AGGREGATE vs SUBTOTAL?
More options / error ignore.
options 6?
Ignore errors.
LARGE?
function 14 + k.

SUBTOTAL, SUM, AVERAGE, LARGE

Practice tasks (10)

  1. Task 1. SUM ignore errors.
    9,6
  2. Task 2. AVERAGE ignore errors.
    1,6
  3. Task 3. LARGE 2nd.
    14
  4. Task 4. Explain vs SUBTOTAL.
    Interview.
  5. Task 5. Show SUM #N/A fail.
    Contrast.
  6. Task 6. Fill.
    Fill.
  7. Task 7. Doc options.
    Note.
  8. Task 8. Teach.
    Teach.
  9. Task 9. COUNT nums.
    2.
  10. Task 10. LO fallback note.
    LO.

Quick quiz

Q1. options 6 means?
Q2. function 9?
Q3. 2nd largest uses?
Q4. Why not plain SUM with #N/A?
Q5. Excel version?

Answers: Q1=Ignore errors, Q2=SUM, Q3=14 + k, Q4=SUM fails on errors, Q5=2010+ (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: AGGREGATE
AGGREGATE revision card