Excel Conditional Aggregates — Free Excel Tutorial
Learn Excel Conditional Aggregates in Excel with a free, beginner-friendly tutorial, examples and practice for Indian students on Syllab.in.
TL;DR: Learn Excel Conditional Aggregates in Excel with a free, beginner-friendly tutorial, examples and practice for Indian students on Syllab.in.
Written & reviewed by the Syllab.in Academic Team (CBSE/NCERT subject experts) · Updated
Excel Conditional Aggregates in Excel
Often you want to summarise only the rows that meet a condition: total sales for one region, count of students who passed, average marks for one class. The …IF family does exactly this. COUNTIF(range, criteria) counts matching cells; SUMIF(range, criteria, sum_range) adds a matching column; AVERAGEIF works the same for the mean.
Criteria can be a value ("Delhi"), a comparison in quotes (">=40"), or a wildcard ("A*" = starts with A). For multiple conditions use the plural versions: SUMIFS, COUNTIFS, AVERAGEIFS, where you list range/criteria pairs — e.g. sales in Delhi AND above ₹500.
These functions replace hours of manual filtering and are the foundation of any dashboard. They are also far more transparent than a pivot table for a single number you want to show in a report cell.
Excel Conditional Aggregates — Syntax
=COUNTIF(B2:B20, ">=40") =SUMIF(A2:A20, "Delhi", C2:C20) =SUMIFS(C2:C20, A2:A20, "Delhi", C2:C20, ">500")
Learn Excel Conditional Aggregates step by step with Syllab's free interactive Excel tutorial — runnable code examples, practice exercises and instant AI feedback, all free with no signup. Explore the full Excel course →