Counting & Summarizing
Count and sum with conditions, percentages, and running totals.
Count Rows That Meet Multiple Conditions
Use COUNTIFS to count rows matching several conditions at once — like overdue tasks in one department — in Excel and Google Sheets.
=COUNTIFS(A2:A20,"Sales",C2:C20,"Overdue")Sum Values That Meet Multiple Conditions
Use SUMIFS to total only the rows that match your conditions — like sales for one region above a threshold — in Excel and Google Sheets.
=SUMIFS(C2:C20,A2:A20,"Sales",B2:B20,">100")Calculate a Completion Percentage
Divide completed items by total items with COUNTIF and COUNTA to get a live completion rate for tasks, orders, or projects.
=COUNTIF(C2:C11,"Complete")/COUNTA(C2:C11)Count How Many Times Each Value Appears
Put a count next to every row showing how many times its value appears in the column — any count above 1 means it's a duplicate.
=COUNTIF(A:A,A2)Count Rows With a Specific Status
Count how many rows in a status column say Complete — or any other exact text — with a single COUNTIF formula.
=COUNTIF(C2:C20,"Complete")Count Blank Cells in a Range
Get a single number showing how many cells in a range are empty — a quick data-completeness check with COUNTBLANK.
=COUNTBLANK(A2:A20)Calculate Percentage Change Between Two Values
Measure growth or decline between two numbers with (new-old)/old, formatted as a percent — the standard month-over-month math.
=(B2-A2)/A2Sum Values for a Single Month
Total every transaction that falls inside one month by bracketing SUMIFS between the first of the month and the first of the next.
=SUMIFS(C:C,B:B,">="&DATE(2026,7,1),B:B,"<"&DATE(2026,8,1))Rank Values from Highest to Lowest
Assign each row its position in the list — 1 for the biggest number — with RANK, without sorting the data itself.
=RANK(B2,$B$2:$B$20,0)Calculate a Running Total Down a Column
Build a cumulative sum with SUM($B$2:B2) — the anchored-start range expands one row at a time as you fill down.
=SUM($B$2:B2)Find the Highest Value in a Category
MAXIFS returns the largest number that matches a condition — like the biggest deal in the Sales department — in one formula.
=MAXIFS(C:C,A:A,"Sales")Find the Lowest Value in a Category
MINIFS returns the smallest number that matches a condition — the cheapest quote, the earliest date, the lowest score per group.
=MINIFS(C:C,A:A,"Sales")Calculate Each Item's Percentage of the Total
Divide each row by the grand total with an anchored SUM to see what share each category, product, or region contributes.
=B2/SUM($B$2:$B$6)Calculate a Weighted Average
SUMPRODUCT divided by SUM gives an average where big items count more — the correct math for average price, blended rates, and scores.
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)Calculate an Average by Category
AVERAGEIF averages only the rows matching a condition — average deal size per region, spend per vendor, hours per project.
=AVERAGEIF(A:A,"Sales",C:C)Count Unique Values in a Column
How many different customers, products, or codes does a column contain? One formula per platform — plus the classic that works everywhere.
=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))COUNTIF vs COUNTIFS: One Condition or Many
COUNTIF counts rows matching one condition; COUNTIFS handles any number — and works identically with one, so many people just always use COUNTIFS.
=COUNTIFS(A2:A50,"East",B2:B50,">1000")SUMIF vs SUMIFS: The Argument-Order Trap
SUMIF puts the sum range LAST; SUMIFS puts it FIRST. That reversal is the single biggest source of broken conditional totals — here's both patterns.
=SUMIFS(B2:B50,A2:A50,"East",B2:B50,">1000")