Advanced Formulas

Conditional Aggregates

=SUMIF(A:A,"Delhi",C:C)          sum C where A is Delhi
=SUMIFS(C:C, A:A,"Delhi", B:B,"Paid")   multiple criteria
=COUNTIF(A:A,">100")              count cells over 100
=COUNTIFS(B:B,">"&E1, C:C,"<="&E2)

INDEX / MATCH - the Flexible Lookup

=INDEX(ReturnRange, MATCH(lookup, LookupRange, 0))

MATCH finds the position; INDEX pulls the value from that row/column. More flexible than VLOOKUP (works left-to-right) and faster on big tables.

SUMPRODUCT - Weighted and Nested Logic

=SUMPRODUCT((A:A="East")*(C:C>0)*D:D)   → sums D for East rows with C>0

The multiplication acts like AND for booleans - one formula, no array entry needed.

Nesting and Protecting

• Keep IF chains short; prefer IFS or lookup tables with MATCH.
• Use =LET(x, A1*B1, x*0.9) (Excel 365) to name intermediate results and avoid repeating yourself.
• Format formulas complex → simplify with helper columns; readability beats cleverness.

Array and Spill Formulas

Excel 365 formulas spill: =UNIQUE(A:A) returns the distinct list filling cells automatically. =FILTER(A:C,B:B="Active") returns matching rows. No Ctrl+Shift+Enter needed.

Key Points

• SUMIFS/COUNTIFS cover 90% of conditional aggregation.
• INDEX/MATCH and XLOOKUP are the reference lookups of choice.
• Spill functions (UNIQUE, FILTER, LET) are the modern way to build.