Excel Hacks in COUNTIF and AGGREGATE:

📊 COUNTIF with Nested Ranges: Running Counts Made Easy 📊

Most people think of COUNTIF as a simple way to count values.

But here’s a powerful twist:

use it with nested ranges to calculate running counts.

=LET(a,A2:A15,COUNTIF(TAKE(a,SEQUENCE(ROWS(a))),a))

• The range argument expands step by step (TAKE).

• The criteria argument matches each element in the range (a).

• For each row, COUNTIF picks the corresponding criterion and calculates its occurrence up to that point.

🚀 Excel Hack: Min & Max in One Go

Need both the smallest and largest values that meet a condition?

Instead of writing two formulas, let AGGREGATE do the heavy lifting — and it even ignores error values like '#N/A' or '#DIV/0!'

=AGGREGATE({15,14},6,B2:B10/(A2:A10=E3),1)

👉 This spills two results:

SMALL (15) → minimum

LARGE (14) → maximum

One formula, two answers. Clean, efficient, and perfect for dashboards or quick checks.

💡 Try it in your next model and see how much time you save!