Learn one useful thing today.
SUBSTITUTE vs REPLACE: swap matching text or overwrite by position
Use SUBSTITUTE when you know which text to swap (all occurrences or a chosen Nth match), and REPLACE when you know the character start index and length.
Read the guide →MathSubtotal by invoice number on first row only (Prevent double-counting fees)
Show one merchandise subtotal and one repeated invoice-level freight charge per invoice, with an explicit consistency check.
Read the guide →FinancialSubtotal open invoices by invoice-age brackets with SUMIFS
Aggregate open invoice balances into standard 30-day AR aging subtotal buckets using elapsed age DaysOld = AuditDate - InvoiceDate and SUMIFS.
Read the guide →Economics & Market MicrostructureSuits Index of Tax Progressivity (S) Formula: Tax-vs-Income Trapezoidal Integral
Calculate Daniel B. Suits’s (1977) Tax Progressivity Index S = 1 - SUM((T_i + T_{i-1}) * (Y_i - Y_{i-1})) across income brackets (-1 regressive to +1 ultra-progressive, 0 flat tax) directly from cumulative income and tax shares.
Read the guide →Date & TimeSum & Average Daily Revenue by Day of Week (WEEKDAY + SUMIF)
Aggregate daily POS or e-commerce transactions by ISO day of week (Monday = 1 through Sunday = 7) and split weekday vs. weekend totals using WEEKDAY(A2, 2) + SUMIF.
Read the guide →Dynamic ArraysSum an entire 2D matrix across columns matching a header tag (SUM & FILTER)
Total a multi-row, multi-column 2D table (B3:E5) across ONLY the columns whose Row-2 status tag equals "Active"—solving the classic 2D SUMIF limitation cleanly!
Read the guide →Date & TimeSum and average by calendar month across years
Group dated observations by month and compare a group average with the overall sample average.
Read the guide →Dynamic ArraysSum and average the last N columns horizontally (rolling trailing months with TAKE)
Calculate trailing 3-month or trailing N-period sums and averages across horizontal monthly P&L columns using TAKE(row, , -N).
Read the guide →MathSum and count payments by calendar year, including December 31 timestamps
Use an inclusive January 1 start and exclusive next-January-1 end to include every timestamp in the selected year.
Read the guide →Math & TrigSum and find MAX of every Nth horizontal column (repeating Budget vs Actual blocks)
Sum or find the maximum across columns selected by repeated headers, such as Actual and Budget.
Read the guide →Date & TimeSum by fiscal year (any fiscal start month: July, October, or April)
Calculate Fiscal Year start and end dates dynamically from FY number and StartMonth (e.g., FY2026 = Jul 1, 2025 to Jun 30, 2026) inside SUMIFS.
Read the guide →Math & TrigSum by month and year with SUMIFS and EOMONTH
Aggregate daily transaction logs into monthly totals dynamically from a Year and Month cell without helper columns.
Read the guide →Date & TimeSum durations above a minute threshold: full durations versus excess minutes
Compare the total duration of qualifying sessions with only their excess minutes, including thresholds longer than one day.
Read the guide →MathSum every 3 cells into quarterly totals (OFFSET & step multiplier)
Roll up a 12-row monthly ledger into 4 consecutive 3-month quarterly totals automatically using SUM(OFFSET($B$2, (Quarter - 1) * 3, 0, 3, 1)).
Read the guide →MathSum every Nth row: total periodic intervals with MOD, ROW, and SUMIF
Tag every Nth row cleanly with MOD(ROW - FirstRow + 1, N) and total matching cycle rows with SUMIF.
Read the guide →Math & TrigSum first N (or last N) matching values by customer (TAKE + FILTER)
Sum the first or last N matching orders in a chronologically sorted transaction log using TAKE and FILTER.
Read the guide →Auditing & CollaborationSum Formula-Driven Cells Only While Isolating Hardcoded Manual Overrides (ISFORMULA)
Separate formula-calculated subtotals from hardcoded manual number overrides using ISFORMULA and SUMIF so shared financial models stay 100% auditable.
Read the guide →Summing & AggregationSum If Begins With Prefix or Ends With Suffix (Wildcard SUMIFS Formula)
Total general-ledger accounts or SKU codes starting with a prefix (Prefix & "*") or ending with a regional/currency suffix ("*" & Suffix)—or matching BOTH simultaneously—using wildcard SUMIFS.
Read the guide →Conditional AggregationSum If Blank ("") vs. Not Blank ("<>") With SUMIF & SUMIFS
Total open accounts-receivable invoices where Paid_Date is blank using SUMIF(D2:D6, "", C2:C6), settled invoices where Paid_Date is non-blank using "<>", and multi-criteria open region balances with SUMIFS.
Read the guide →Math & TrigSum If Cells Contain Both Keywords (AND) or Either Keyword (OR)
Sum rows containing both or either literal text substrings, escape wildcard characters, and avoid double-counting the intersection without silently accepting empty keywords.
Read the guide →MathSum If Cells Contain Text from Another Cell ("*" & Keyword & "*")
Total invoices or bank transactions whose description contains a dynamic search keyword in E2 using wildcard concatenation "*" & E2 & "*"—with an empty-search-box ISBLANK guard so blank inputs do not sum the entire sheet!
Read the guide →MathSum If Not Equal To ("<>") Single or Multiple Exclusions (SUMIFS)
Total ledger amounts while excluding cancelled, refunded, or intercompany categories using SUMIF(Cat, "<>" & Excl, Amt) and multi-exclusion SUMIFS(Amt, Cat, "<>" & Excl1, Cat, "<>" & Excl2).
Read the guide →Math & TrigSum matching columns across repeating headers (Actual vs Budget)
Total alternating Actual and Budget columns horizontally on one row with SUMIF or across an entire 2D table with SUMPRODUCT.
Read the guide →Math & TrigSum matching rows AND columns in a 2D matrix (SUMPRODUCT outer product)
Aggregate a 2D crosstab when BOTH row labels and column headers can repeat (e.g., multiple "North" rows across multiple "Q1" columns).
Read the guide →Statistical & EngineeringSum of Squared Deviations (DEVSQ) & Pooled Within-Group Variance Formula
Compute the exact Sum of Squared Deviations from the sample mean SS = (COUNT(range) - 1) * VAR.S(range) and combine two independent group SS terms into a pooled ANOVA / t-test variance.
Read the guide →Math & EngineeringSum of Squares & Paired Vector Differences (SUMSQ, SUMXMY2, SUMX2MY2 & SUMX2PY2)
Compute vector sum of squares (SUMSQ), squared Euclidean distance (SUMXMY2), difference of squares (SUMX2MY2), and combined squared energy (SUMX2PY2) transparently with SUMPRODUCT.
Read the guide →Math & TrigSum or average numbers stored inside a single comma-separated cell
Total, average, or count comma- or semicolon-delimited numbers in one cell ("120, 85, 45, 150" -> 400) using TEXTSPLIT and numeric coercion.
Read the guide →Math & TrigSum or average the last N entries in a column (skipping blank rows)
Extract the final N numeric observations from an expanding log with TAKE and FILTER—keeping legitimate zeros while skipping trailing blanks.
Read the guide →Math & TrigSum or count whole numbers only (exclude decimal fractions)
Flag integer vs fractional entries with MOD(number, 1) = 0 and aggregate full-unit vs partial-unit rows cleanly with SUMIFS and COUNTIF.
Read the guide →Statistical & Conditional CountingSum Text Values Like Numbers (T-Shirt Story Points & Ordinal Rating Scales)
Total and average categorical text codes such as XS/S/M/L/XL story points or Low/Medium/High risk tiers by pairing a compact lookup legend with COUNTIF frequencies and SUMPRODUCT.
Read the guide →Dynamic Arrays & LookupSum the Bottom N Values Matching Criteria (FILTER, SORT & TAKE Pipeline)
Total the N lowest vendor quotes, fastest lap times, or smallest Regional invoices within a specific category using SUM(TAKE(SORT(FILTER(...)), N)) with graceful IFERROR handling.
Read the guide →Date & TimeSum the last 30 days (or rolling N days) with SUMIFS
Total revenue or orders inside a rolling 30-day window and avoid the classic RefDate - 30 vs RefDate - 29 off-by-one boundary mistake.
Read the guide →Date & TimeSum timesheet hours by week and project (7-day window SUMIFS)
Roll up daily employee time entries into weekly project totals using half-open [WeekStart, WeekStart + 7) date criteria.
Read the guide →Math & TrigSum top N (or bottom N) values with criteria: FILTER + SORT + TAKE
Total the top K largest deals inside a specific region—even when that region has fewer than K rows—by chaining FILTER, SORT, and TAKE.
Read the guide →Math & TrigSum top N or bottom N values (overall and with criteria)
Total the N largest deals or N lowest vendor quotes dynamically using SUM, TAKE, SORT, and FILTER.
Read the guide →Text FunctionsSum, Average & Count Comma-Delimited Numbers Inside One Cell (TEXTSPLIT)
Parse comma- or pipe-delimited numeric lists packed inside a single cell (such as "120, 85, 95, 140") into real numbers using --TEXTSPLIT(B2, ",") and aggregate them with SUM, AVERAGE, MIN, and MAX.
Read the guide →Math & TrigSum, average, or find MAX while ignoring formula errors (#DIV/0!, #N/A)
Keep a single #DIV/0! or #N/A cell from poisoning summary KPIs by filtering ranges with ISNUMBER before SUM, AVERAGE, or MAX.
Read the guide →Lookup & ReferenceSum, Max & Average a Variable Column by Header (CHOOSECOLS + MATCH)
Select a numeric column by a quarter header and calculate its sum, maximum, minimum and average with missing-header checks.
Read the guide →MathSUMIF & COUNTIF when cells contain a literal asterisk (*) or question mark (?)
Escape wildcard characters with a tilde ("*~**") so SUMIF and COUNTIF match literal asterisks, question marks, or tildes instead of every text row.
Read the guide →Conditional totalsSUMIF in Excel: total a category without losing refunds
Build a category total from a community-garden expense log. Keep negative returns, change the selected category, and check a zero result against the source rows.
Read the guide →MathSUMIFS & SUMIF across horizontal columns (multi-row column headers)
Sum wide horizontal budget/actual rows left-to-right where Row 1 Quarter and Row 2 Scenario headers match criteria—without transposing—using horizontal SUMIFS.
Read the guide →Lookup & ReferenceSUMIFS & TEXTJOIN+FILTER vs XLOOKUP When Duplicate Keys Exist
Choose deliberately between one matching row, the sum of every matching amount, and a joined list of descriptions. Test literal invoice IDs, missing keys and zero totals.
Read the guide →Dates and reportingSUMIFS between two dates: total amounts inside a reporting window
Add invoice payments on or after a start date and on or before an end date by joining comparison operators to boundary cells with &. Verify both endpoints and channel filters.
Read the guide →Dates and reportingSUMIFS time totals: add shift durations past 24 hours without losing a day
Spreadsheets store time durations as fractions of a 24-hour day. Sum shifts by team with SUMIFS and multiply by 24 for decimal hours—avoiding the classic clock-rollover bug where 27 hours looks like 3:00.
Read the guide →Conditional AggregationSUMIFS with Multiple Criteria and OR Logic Without Double-Counting Rows
Distinguish safe same-column OR addition in SUMIFS from overlapping cross-column OR conditions where naive SUMIF + SUMIF double-counts rows that satisfy both rules.
Read the guide →Conditional totalsSUMIFS: total sales that match two conditions
A sales total is only useful when it includes the right orders. Use SUMIFS to add amounts for one region and one order status, without hiding or deleting the other rows.
Read the guide →Statistical & Mathematical FormulasSummarize Survey Results by Group: Top-2-Box % & Net Favorability (COUNTIFS)
Aggregate 1-to-5 CSAT or employee-pulse survey responses by department into Top-2-Box satisfaction percentage (scores 4 & 5) and Net Favorability (% Top-2 minus % Bottom-2) using COUNTIFS.
Read the guide →MathSUMPRODUCT with criteria: multiply and filter arrays without a helper column
Calculate filtered revenue across quantity and unit-price columns in one SUMPRODUCT formula using boolean (TRUE/FALSE) array multiplication.
Read the guide →Charts & Visual DataSunburst Chart Formula: Hierarchical Ring Angles (360°), Parent-Child Rollups & Gap Audit
Prepare and audit multi-level hierarchical data for a concentric Sunburst Chart by computing inner parent-ring rollups, outer leaf-ring 360-degree arc spans, and within-parent conditional shares.
Read the guide →Engineering & ScienceSunyaev-Zel’dovich Effect: Cluster Compton-y & CMB Temperature Decrement
Calculate the redshift-independent galaxy-cluster Compton-y parameter y = 0.0040171 * n_e * T_keV * L_Mpc and Rayleigh-Jeans CMB brightness shadow ΔT_RJ = -2 * y * T_CMB (μK).
Read the guide →