📊 Excel Formulas — Part 8

This part focuses on Advanced Calculation Functions — useful for working with filtered data, multiple calculations, and large datasets.

1️⃣ SUBTOTAL

Performs calculations while respecting filtered or hidden rows depending on the function number.

=SUBTOTAL(9,B2:B100)

Here, 9 means SUM.

Common function numbers:

1 → AVERAGE

2 → COUNT

3 → COUNTA

9 → SUM

4 → MAX

5 → MIN

💡 Very useful when working with filtered tables.

2️⃣ AGGREGATE

Performs calculations while allowing you to ignore errors, hidden rows, or nested subtotals.

=AGGREGATE(9,5,B2:B100)

Here:

9 → SUM

5 → Ignore hidden rows

It supports functions such as:

• AVERAGE

• COUNT

• MAX

• MIN

• SUM

• LARGE

• SMALL

3️⃣ SUMPRODUCT

Multiplies corresponding values and then adds the results.

=SUMPRODUCT(B2:B10,C2:C10)

Example:

Product Price Quantity

Laptop 50000 2

Mouse 800 5

Keyboard 1500 3

=SUMPRODUCT(B2:B4,C2:C4)

This calculates:

50000×2 + 800×5 + 1500×3

Result → 107,500

💡 Extremely useful for weighted calculations and business analysis.

4️⃣ LARGE

Returns the nth largest value.

=LARGE(B2:B10,1)

→ Largest value

=LARGE(B2:B10,2)

→ 2nd largest value

5️⃣ SMALL

Returns the nth smallest value.

=SMALL(B2:B10,1)

→ Smallest value

=SMALL(B2:B10,2)

→ 2nd smallest value

6️⃣ RANK.EQ

Returns the rank of a number within a dataset.

=RANK.EQ(B2,$B$2:$B$10,0)

0 → Highest value gets rank 1

1 → Lowest value gets rank 1

Example:

Sales = 95000

Rank → 2

7️⃣ SUMPRODUCT + Conditions

SUMPRODUCT can also perform conditional calculations.

=SUMPRODUCT((A2:A10="East")*(B2:B10))

This calculates the total of values in column B where the region is East.

💡 Useful when you need flexible calculations without creating helper columns.

🧠 Quick Reference

SUBTOTAL → Calculations that work well with filtered data

AGGREGATE → Advanced calculations with options to ignore certain values

SUMPRODUCT → Multiply and sum corresponding values

LARGE → nth largest value

SMALL → nth smallest value

RANK.EQ → Rank values

SUMPRODUCT + Conditions → Flexible conditional calculations

💡 Practice

Using a sales dataset, try to calculate:

1.

Total visible sales → SUBTOTAL

2.

Average visible sales → SUBTOTAL

3.

Sum while ignoring hidden rows → AGGREGATE

4.

Total revenue from Price × Quantity → SUMPRODUCT

5.

3rd highest sale → LARGE

6.

2nd lowest sale → SMALL

7.

Rank each salesperson → RANK.EQ

8.

Total East-region sales → SUMPRODUCT

🔥 Double Tap ❤️ For More