Learn one useful thing today.
Sum 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 →EngineeringSupersonic Mach Cone Angle & Subsonic Prandtl-Glauert Rule
Calculate the Mach-wave half-angle for supersonic flow and a separate Prandtl-Glauert pressure-coefficient approximation. Distinguish weak waves, finite-strength shocks and the sonic limit.
Read the guide →EngineeringSupersonic Normal Shock Wave: Downstream Mach & Rankine-Hugoniot Jumps
Calculate supersonic normal shock downstream Mach number M_2 = SQRT((1 + ((g-1)/2)*M1^2) / (g*M1^2 - (g-1)/2)) and static pressure jump P2/P1 = 1 + (2*g/(g+1))*(M1^2 - 1) for compressible gas flows.
Read the guide →StatisticalSupply Chain Bullwhip Effect Ratio: order variance amplification & lead-time bound
Measure supply-chain order distortion via Bullwhip Variance Ratio VAR.S(Orders) / VAR.S(Demand), CV Ratio, and Chen-Simchi-Levi moving-average lower bound.
Read the guide →Precision Abrasive Machining, Surface/Creep-Feed Grinding & Malkin Grinding BurnSurface Grinding Specific Energy, MRR & Burn Power Formula
Calculate Volumetric Grinding Removal Rate Qw (mm³/s), Specific Removal Rate Per Wheel Width Q'w (mm³/(mm·s)), Grinding Spindle Power (kW), and Workpiece Heat-Partition Burn Load (kW).
Read the guide →FinancialSustainable Growth Rate (SGR) & Internal Growth Rate (IGR) corporate finance formulas
Explore constant-ratio financing models for growth, distinguishing beginning-equity ROE from ending-equity ROE and internal funding from constant-leverage funding.
Read the guide →EngineeringSutherland's Law for Gas Dynamic Viscosity vs Temperature
Calculate gas dynamic viscosity with Sutherland’s temperature correlation, using a documented air parameter set, raw outputs and guards for Kelvin temperatures and reference inputs.
Read the guide →Engineering & ScientificSutherland’s Law for Temperature-Dependent Gas Dynamic Viscosity
Model gas dynamic viscosity mu(T) across wide Kelvin temperature spans using Sutherland’s three-coefficient molecular-potential law for HVAC, flue-gas, and aerodynamic Reynolds numbers.
Read the guide →