Sum Columns Based on Adjacent Criteria

This calculator sums the values in one column when a neighboring column meets a condition you specify. Enter your data range, choose the criteria column and the sum column, and set the condition (e.g., equals, greater than, contains text); the tool returns the total that matches. It works for both numeric and text criteria, and it handles blank cells and errors gracefully.

Formula

The calculation uses a conditional sum: SUMIF(range, criteria, [sum_range]) in spreadsheet terms, or in plain language: add up all numbers in the sum column where the corresponding cell in the criteria column satisfies the condition. The formula is:

Total = Σ (sum_column_value) for each row where criteria_column condition is TRUE

Variables:

• criteria_column – the column you check against the condition (e.g., A).
• sum_column – the column whose values you add (e.g., B).
• operator – the comparison: equals, not equal, greater than, less than, or contains (for text).
• criteria value – the number or text you compare against.

For “greater than� or “less than,� the criteria value must be numeric. For “contains,� it treats the criteria as text and sums rows where the criteria cell contains that substring (case-insensitive).

Worked Example

Using the default inputs: data range A1:B10, criteria column A, sum column B, operator “equals,� criteria value 10.

Suppose your data is:

A	B
10	5
20	3
10	7
30	2
10	4
40	1
10	6
50	8
10	9
60	0

Rows where A equals 10 are rows 1, 3, 5, 7, and 9. Their B values are 5, 7, 4, 6, and 9. Sum = 5 + 7 + 4 + 6 + 9 = 31. The calculator returns 31.00.

When to Use This

This is the classic “sum if� problem that appears in budgeting, inventory, sales reports, and academic grading. You have two columns: one with categories or conditions (e.g., product type, date, region, pass/fail) and one with amounts. You want the total for a specific category, not the overall total. It’s also useful for filtering out unwanted rows when you only need a subtotal.

How to Choose the Criteria Column and Operator

• Equals – use for exact matches: a specific product ID, a date, a name, or a status like “Done.â€�
• Not equal – use to sum everything except a certain value, e.g., all sales except returns.
• Greater than / Less than – use for numeric thresholds: sales above $500, ages below 18, dates after a certain day (if dates are stored as numbers).
• Contains – use for partial text matches: sum all rows where a product name contains “Proâ€� or a note contains “urgent.â€�

Make sure the criteria column and sum column are the same length. If they aren’t, the calculator will only use the overlapping range.

Common Mistakes and How to Avoid Them

• Mixing up columns – double-check which column holds the criteria and which holds the values. A common error is swapping them, which gives a totally different result.
• Text vs. number – if your criteria column contains numbers stored as text (e.g., with a leading apostrophe), “equalsâ€� won’t match a numeric value. Ensure consistent data types.
• Blank cells – blank cells in the criteria column are ignored for “equalsâ€� (they don’t match anything), but they are included for “not equalâ€� (blank is not equal to the criteria). This often surprises users.
• Errors in the sum column – if any cell in the sum column contains an error (like #DIV/0!), the total becomes an error. The calculator does not skip errors; clean your data first.
• Case sensitivity – “containsâ€� is case-insensitive, but “equalsâ€� is case-sensitive for text. “Appleâ€� and “appleâ€� are different.

Limitations of the Formula

This calculator handles a single condition. If you need to sum based on multiple criteria (e.g., region = West AND quarter = Q1), you would need a different approach, such as SUMIFS in a spreadsheet. Also, the “greater than� and “less than� operators only work with numeric criteria; for dates, you must convert them to serial numbers or use text comparison if your dates are in a sortable format. The “contains� operator only works on text; it will not match numbers unless they are formatted as text.

Related Calculations

If you need to count rows instead of summing values, use COUNTIF. If you need an average of matching values, use AVERAGEIF. If you need to sum with multiple conditions, use SUMIFS. This calculator is intentionally simple to cover the most common single-criteria case.

Frequently Asked Questions

Can I sum based on a condition in a different column than the one I’m summing?

Yes, that’s exactly what this calculator does. You specify the criteria column (where the condition is checked) and the sum column (where the numbers come from). They can be the same column, but usually they are different.

What if my criteria is text and I want to sum all rows that contain a word?

Use the “contains� operator. For example, to sum all sales where the product name contains “Pro�, set operator to “contains� and criteria value to “Pro�. It will match “Pro�, “ProMax�, “Pro Lite�, etc., case-insensitively.

How do I sum all values except those that match a certain number?

Use the “not equal� operator. For instance, to sum all sales except returns (where return is coded as 0), set operator to “not equal� and criteria value to 0.

What if my data has blank cells in the criteria column?

Blank cells are treated as empty. For “equals�, they won’t match anything (unless you set criteria to blank, which isn’t supported). For “not equal�, they will be included because blank is not equal to your criteria. For “greater than� or “less than�, blank cells are ignored (they don’t match).

Can I use this calculator for dates?

Yes, if your dates are stored as numbers (e.g., Excel serial numbers) or as text in a consistent format. For “greater than� or “less than�, ensure the dates are in a comparable format (like YYYY-MM-DD). For “equals�, exact text match works if the format is identical.

Why is my total showing 0 even though I see matching rows?

Check that the criteria column and sum column are correctly specified. Also verify the operator and criteria value match your data exactly. For text, “equals� is case-sensitive. For numbers, ensure there are no hidden spaces or text-formatted numbers. Finally, confirm that the sum column contains numeric values, not text.

Related

• Remove Mac Auto Fixer? Safe Steps & What to Know
• Fraction Solver with Solution Steps
• MKV, MP4 and M4V Video Converters: 10 Top Picks

in data | Data range (A1:B10) | - | A1:B10 | 0
in crit_col | Criteria column (A or B) | - | A | 0
in sum_col | Sum column (A or B) | - | B | 0
in op | Operator | equals=eq, not equal=ne, greater than=gt, less than=lt, contains=contains | equals | 0
in val | Criteria value | - | 10 | 0
out total | Sum of matching values | - | sum(if(eq, data, crit_col, val)) | 2