SUMPRODUCT function

Syntax

Square brackets indicate optional arguments.

Arguments

Practice a complete task

• Find and flag duplicate values →

  Mark every repeated registration, keep blank rows blank, and see which values need a closer look.

• Weight a workload by its case counts →

  Combine per-case estimates using the number of cases instead of giving every service the same influence.

• Build an operational review with the right denominator →

  Compare two legitimate averages for one selected population and base the review flag on the agreed case-weighted metric.

• Compare a chosen percentile with an allowance →

  Calculate an inclusive percentile, count observations strictly above it and apply an explicit allowance rule.

• Flag observations outside the IQR fences →

  Calculate inclusive quartiles and two 1.5-IQR fences, then count measured values strictly outside them.

• Extract both sides of the final separator →

  Use the last literal separator to separate a record prefix from its final field without losing earlier separators or repeated records.

• Normalize and review structured reference codes →

  Create a three-column audit that validates a declared ASCII code shape before extracting a four-character suffix.

• Sum repeated payments for each invoice →

  Combine split receipts and refunds before calculating each invoice balance; keep unmatched receipts visible in the source.

• Reconcile grouped totals from two exports →

  Keep the union of both ID lists, combine signed repeated rows on each side, then report both totals and their difference.

• Group posted net units by region →

  Filter order status before summing repeated regions, including returns and numeric zeroes.

• Cross-tabulate posted order means →

  Build a region-by-channel mean report whose row, column and grand means use the original records.

• Report each group share of net units →

  Make the grand-total denominator explicit, retaining signed returns, missing intersections and a zero-net state.

• Return the last N active records →

  Skip empty-ID slots, reorder the fields and take the final requested records without sorting or reversing them.

• Summarize measurements that contain errors →

  Report a signed total, numeric count, numeric mean and genuine error count without converting other value types to zero.

• Evaluate dated cash flows using actual day intervals →

  Keep date-value pairs aligned and use 365-day discounting for an irregular cash-flow schedule.

• Explain a modified return with finance and reinvestment rates →

  Separate the present cost of negative flows from the terminal value of positive flows, then reconcile MIRR.

• Look up a rate with multiple criteria →

  Match three fields on the same row, return the first rate, and count complete matches before treating the key as unique.

• Count numbers, text, logical values and blank-like cells →

  Reconcile four disjoint stored types and explain why COUNTA plus COUNTBLANK can exceed the number of cells.

• Compare mean populations without changing stored types →

  Keep numeric zeroes, excluded text and explicit mean denominators visible before choosing an averaging rule.

Reference and compatibility

• Microsoft function documentation ↗

Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.