Our 10 featured functions

SUMIFS

Matches all 21 recorded answers

Recorded: 21 workbook cells. How we measure this · See the Rust code

Local names in 40 languages
LanguageName in Excel
ArabicSUMIFS
BulgarianSUMIFS
CzechSUMIFS
DanishSUM.HVISER
GermanSUMMEWENNS
GreekSUMIFS
English (Australia)SUMIFS
English (United Kingdom)SUMIFS
English (United States)SUMIFS
SpanishSUMAR.SI.CONJUNTO
EstonianFunktsioon SUMIFS
FinnishSUMMA.JOS.JOUKKO
FrenchSOMME.SI.ENS
HebrewSUMIFS
CroatianSUMIFS
HungarianSZUMHATÖBB
IndonesianSUMIFS
ItalianSOMMA.PIÙ.SE
JapaneseSUMIFS
KoreanSUMIFS
LithuanianSUMIFS
LatvianSUMIFS
NorwegianSUMMER.HVIS.SETT
DutchSOMMEN.ALS
PolishSUMA.WARUNKÓW
Portuguese (Brazil)SOMASES
Portuguese (Portugal)SOMA.SE.S
RomanianSUMIFS
RussianСУММЕСЛИМН
SlovakSUMIFS
SlovenianSUMIFS
Serbian (Latin)SUMIFS
SwedishSUMMA.OMF
ThaiSUMIFS
TurkishÇOKETOPLA
UkrainianSUMIFS
VietnameseSUMIFS
Chinese (Simplified)SUMIFS
Chinese (Hong Kong)SUMIFS
Chinese (Traditional)SUMIFS

Names from Microsoft's support site.

What it does

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

SUMIFS(sum_range, criteria_range1, criteria1, ...) adds amounts whose corresponding cells satisfy all supplied conditions. The amount range is the first argument.

Try it

Input cells (optional)

Example: {"B2": 3, "B3": 4}. A1 holds the formula. Other cells need values before a formula can read them.

Enter a formula, then calculate.

Runs locally in your browser with OneCalc. Its answer may differ from Excel. Use English function names and commas between arguments.

With amounts 4,11,7,13 in B2:B5 and regions east,east,west,east in C2:C5, =SUMIFS(B2:B5,C2:C5,"east",B2:B5,">=10") returns 24: eleven plus thirteen.

Quirks

Every criteria range must have the same shape as the amount range. Wildcards work in text criteria. The ordering differs from SUMIF, where the sum range is optional and comes last. Multiple criteria mean AND; adding two SUMIFS calls can count a row twice if the conditions overlap.

How to implement

Parse a leading comparison (=, <>, <, <=, >, >=) separately from its operand. Distinguish a numeric comparison from a text pattern. For text patterns support ?, *, and tilde escaping, with Excel’s case-insensitive matching. Keep blank cells, empty strings and zero as distinct inputs until the criteria rule decides how each matches.

Check the dimensions, then walk corresponding offsets. Evaluate every criterion before admitting that position’s amount. Fold only the admitted numeric amounts into the sum. In the example the admitted cells are B3 and B5, yielding 0+11+13=24. Keep criteria matching separate from numeric reduction, so errors and blank rules can be tested independently.

Rust code

Show the CalcFunc source of this function

Quoted from the public repository DnaCalc/DnaCalc, crates calc-func and calc-fml, at commit 9e1884369a67. Comments are the source's own. A kernel shared with other functions appears once, on its module page.

SUMIFS · 1 lines · calc-func/src/catalogue/conditional.rs on GitHub
conditional(SUMIFS, "SUMIFS", (3, 255), SUM_PAIRS, sumifs)
sumifs · 10 lines · calc-func/src/catalogue/conditional.rs on GitHub
/// SUMIFS: SUMIF over the rows that meet every criterion.
fn sumifs(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(SUMIFS, services);
    Site::finish(
        pairs(&site, arguments, services, true).and_then(|plan| total(&site, &plan, services)),
    )
}

Kernels shared with other functions

Argument and result plumbing

No implementation has been published with a Handbook test suite for this function.

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

SUMIF is shorter for one condition; COUNTIFS counts rows; SUMPRODUCT expresses weighted or more general array conditions.

Named variants

No named variants are recorded yet.

Suggest a correction or contribute an implementation · For agents