To total orders above threshold, enter =SUMIF(A1:A3,">"&D1,A1:A3) in F1. With order amounts in A1:A3 of 750, 1250, and 1800 and a threshold of 1000 in D1, F1 returns 3050. Use this when each row has one order amount and the report needs the combined value of orders that are strictly over an approval or reporting limit.
The formula tests every amount in A1:A3 against the value in D1, then adds qualifying amounts from the matching A1:A3 sum range. An amount equal to the limit is deliberately left out.
Orders above threshold: the SUMIF formula
Put the approval limit in D1 and the formula in F1:
=SUMIF(A1:A3,">"&D1,A1:A3)
Input cells: A1:A3 contain order amounts, and D1 contains the changing threshold. Output cell: F1 contains the total of qualifying order amounts.
The ">"&D1 portion joins the greater-than operator to the number held in D1. This matters: the operator and the cell value must form one criterion. Microsoft documents this concatenation pattern for criteria that use operators with a cell reference in its guidance on using cell references in Excel criteria.
Task and setup
Assume a finance team reviews individual orders over 1000. The following is synthetic data for a simple report. Enter the three order amounts in A1:A3, put the limit in D1, and place the formula in F1.
| Cell | Label | Value |
|---|---|---|
| A1 | Order amount | 750 |
| A2 | Order amount | 1250 |
| A3 | Order amount | 1800 |
| D1 | Approval threshold | 1000 |
| F1 | Total above threshold | =SUMIF(A1:A3,">"&D1,A1:A3) |
This synthetic order report uses A1=750, A2=1250, A3=1800, and D1=1000.
F1 returns 3050 because 1250 and 1800 are greater than 1000, while 750 is not. You can change D1 to another numeric limit without editing the formula.
How the formula works
- The first
A1:A3is the range that Excel evaluates, one order amount at a time. ">"&D1creates the criterion>1000when D1 contains 1000.- The final
A1:A3is the matchingsum_range. It contains the order amounts that Excel adds when the corresponding value passes the test.
The first A1:A3 is the range tested against the criterion, and the final A1:A3 is the matching sum range that contains the order amounts to add. Writing both ranges makes the tested range and added range explicit, even though this single-column report uses the same cells for both roles.
Microsoft’s SUMIF function documentation describes the syntax as SUMIF(range, criteria, [sum_range]) and explains that a sum range can differ from the tested range. In this example, the ranges intentionally match.
If your worksheet has a separate condition column and a separate amount column, the setup changes. For example, a status-based conditional total is covered in this guide to conditional sums in Excel. For a broader comparison of counting and summing conditions, see the SUMIF and COUNTIF guide.
Boundary case: the limit itself is not included
Use the same formula, but replace the synthetic values with A1=1000, A2=1001, A3=900, and keep D1=1000:
=SUMIF(A1:A3,">"&D1,A1:A3)
An order exactly equal to the threshold is excluded: when D1 is 1000, A1=1000, A2=1001, and A3=900, F1 returns 1001. Only A2 qualifies because it is strictly greater than the limit.
If policy says that an order of 1000 should qualify as well, use =SUMIF(A1:A3,">="&D1,A1:A3) instead. If the formula uses ">=" instead of ">", an order equal to the threshold is included. Choose the operator from the reporting rule rather than assuming that “above” means “at or above.”
Common mistakes and their fixes
The formula returns 0 although large orders exist
A formula such as =SUMIF(A1:A3,">D1",A1:A3) can return 0 because it treats D1 as text inside the criterion instead of using D1’s numeric value. Fix it by joining the operator and reference: =SUMIF(A1:A3,">"&D1,A1:A3).
The total includes an order at the exact limit
The visible symptom is that an order of 1000 appears in a report intended to show orders over 1000. The cause is using >=. Replace it with > when equality must be excluded.
There are no orders over the limit
No qualifying orders return 0: if D1 is 2000 with A1=750, A2=1250, and A3=1800, F1 returns 0. That is a valid result, not an error. It means no individual order amount exceeded 2000.
Compatibility and limits
Microsoft lists SUMIF for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including the listed Mac versions. The formula uses standard SUMIF syntax rather than a newer dynamic-array function.
This setup totals the order amounts themselves; it does not total a separate column or apply a second condition such as an order status. To total a different numeric column, replace the final A1:A3 with a row-aligned sum range. To require two or more conditions, use SUMIFS rather than trying to combine unrelated tests in one SUMIF criterion.
Next task for a report with separate columns
When the amount being tested is not the amount being added, keep the criteria range and the sum range aligned row for row. For example, a report might test order values in one column but total commissions in another. For this article’s single-column order-total question, using the same range in both positions makes the intended calculation clear.
Synthetic examples are provided for practice. They are not evidence of personal experience, independent human review or execution in Microsoft Excel.