4. Conditional aggregation with SUMIFS, COUNTIFS, and AVERAGEIFS for extracting stress, displacement, and safety factor statistics by part or load case.

In CAE post-processing, the raw result table exported from a solver is rarely the answer. A typical nodal or elemental export contains tens of thousands of rows: one row per node or element, with columns for part ID, load case, stress components, displacement magnitude, and a computed safety factor. The engineering questions, however, are almost always conditional: What is the peak von Mises stress in the bracket under Load Case 3? How many elements in the housing exceed a safety factor of 1.5? What is the mean displacement of the cover plate across all load cases? The SUMIFS, COUNTIFS, and AVERAGEIFS family answers exactly these questions, and it does so without sorting, filtering, or pivot tables — the result updates automatically when the underlying result table is refreshed.

Why the *IFS family, and not SUMIF/COUNTIF

The single-criterion SUMIF, COUNTIF, and AVERAGEIF functions accept one range/criterion pair. CAE result tables almost always require at least two conditions simultaneously — a part and a load case, or a part and a stress threshold. The plural forms accept up to 127 range/criterion pairs and evaluate them with AND logic: a row contributes only if every pair is satisfied. This is the correct semantic for engineering filtering, and it is the reason the *IFS variants should be the default choice in a results workbook.

The syntax is consistent across all three:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Note the asymmetry that trips up many users: in SUMIFS and AVERAGEIFS, the range to be aggregated comes first; in the legacy SUMIF/AVERAGEIF it comes last. COUNTIFS has no aggregation range at all — it simply counts qualifying rows.

A representative result table

Consider a linear static study of an assembly with three parts — BRACKET, HOUSING, and COVER — solved under three load cases: LC1 (operating), LC2 (peak), and LC3 (emergency). The exported table is placed on a sheet named Results, with data starting at row 2 and extending to row 5001 (5,000 element rows). The relevant columns are:

All values shown are illustrative example data, not measured results. The safety factor is defined here as yield strength divided by von Mises stress, so a lower value is worse; this convention must be stated explicitly and kept consistent, because some workflows define SF as strength ratio and others as margin. The displacement magnitude is a scalar, always non-negative, so no sign convention is needed for it.

Extracting statistics by part and load case

A summary block is built with part names down the rows and load cases across the columns. For the peak stress of the bracket under LC2, placed in a cell referenced by row/column headers:

=SUMIFS(Results!$D$2:$D$5001,
         Results!$B$2:$B$5001, $A5,
         Results!$C$2:$C$5001, B$4)

Here $A5 holds the part name and B$4 the load case label. The mixed referencing is deliberate: the part name is locked to its column so the formula can be filled rightwards across load cases, while the load case is locked to its row so it can be filled downwards across parts. This single formula, filled across the whole summary block, produces the complete part × load-case stress matrix.

For the mean displacement, the same structure applies with AVERAGEIFS:

=AVERAGEIFS(Results!$E$2:$E$5001,
             Results!$B$2:$B$5001, $A5,
             Results!$C$2:$C$5001, B$4)

And to count how many elements in each part/load-case combination fall below a safety factor threshold of 1.5 — the "at-risk element count" — COUNTIFS is used with a comparison criterion expressed as a string:

=COUNTIFS(Results!$B$2:$B$5001, $A5,
          Results!$C$2:$C$5001, B$4,
          Results!$F$2:$F$5001, "<1.5")

The criterion "<1.5" is a text expression; Excel parses the operator and compares numerically. If the threshold lives in a cell, say $B$2, concatenate it: "<"&$B$2. This makes the threshold adjustable without editing formulas — a small but important practice for design-space studies where the allowable SF changes between iterations.

Reading the summary correctly

The three functions answer different questions and their outputs must be interpreted together. A high SUMIFS peak with a low COUNTIFS at-risk count indicates a localized stress concentration — a few elements carry the load, which is typical at fillets and contact edges. A high at-risk count with a moderate peak suggests a broadly under-designed region. An AVERAGEIFS value far below the peak confirms the same localization. The figure below shows how the three aggregations draw from the same filtered row set but produce complementary statistics.

Common failure modes and how to diagnose them

The most frequent symptom is a result of 0 where a non-zero value is expected. This almost always means the criterion text does not match the data exactly. Trailing spaces in exported part names, case differences (Bracket vs BRACKET), and numeric-looking load-case labels stored as text are the usual culprits. COUNTIFS with the same criteria is the fastest diagnostic: if the count is zero, the filter is wrong, not the aggregation. Use TRIM on the source column or a helper column to normalize labels before building the summary.

A second symptom is #DIV/0! from AVERAGEIFS. This occurs when no row satisfies the criteria, so the function attempts to divide by a zero count. Wrap it in IFERROR(..., "") or, better, guard with a COUNTIFS check so that a genuinely empty combination is distinguishable from a broken formula. A third symptom is a sum that is suspiciously large — typically caused by overlapping or duplicated criteria ranges, or by summing a column that already contains subtotal rows from the export. Always confirm the data range excludes any footer or total rows the solver appended.

Building a reusable summary block

A practical results workbook separates the raw export from the summary. Place the part list in one column and the load-case list in one row, then write the three formulas once at the top-left of the matrix and fill across and down. Because every formula uses the same mixed references, the entire block stays consistent. Add a threshold cell for the safety-factor criterion and reference it with concatenation, so a design review can sweep the allowable SF from 1.2 to 2.0 and watch the at-risk counts update. Finally, cross-check the block: the sum of all COUNTIFS cells across parts and load cases should equal the total row count of the export, and each SUMIFS peak should be greater than or equal to the corresponding AVERAGEIFS mean. These two invariants catch the majority of range and label errors before the numbers reach a report.