How to Sum Values Based on a Condition in Excel?
Sum Values with Criteria is a Excel function that sums all values in column b where the corresponding value in column a matches the specified criteria.. Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The SUMIF function allows you to sum values in one column based on criteria in another column, simplifying data analysis.
The Formula
"Sum values in column B where column A equals a specific value"
=SUMIF(A:A, "criteria", B:B)This formula sums all values in column B where the corresponding value in column A matches the specified criteria.
Step-by-Step Breakdown
- A:A specifies the range to evaluate for the criteria.
- criteria is the condition that must be met (e.g., a specific text or number).
- B:B specifies the range of values to sum if the criteria is met.
- The function returns the total of all matching values from column B.
Edge Cases & Warnings
- If there are no matches, the formula returns 0.
- If the criteria is a number, ensure it is not enclosed in quotes.
- Using wildcards in the criteria may lead to unexpected results if not formatted correctly.
Examples
"A1:A5 contains {"Apple", "Banana", "Apple", "Orange", "Banana"} and B1:B5 contains {10, 20, 30, 40, 50}, criteria is "Apple""
40"A1:A5 contains {"X", "Y", "X", "Z", "Y"} and B1:B5 contains {5, 15, 25, 35, 45}, criteria is "Y""
60Frequently Asked Questions
Can I use SUMIF with multiple criteria?
For multiple criteria, use the SUMIFS function instead.
What happens if my criteria is not found?
The formula will return 0 if there are no matches.
Can I use cell references for criteria?
Yes, you can reference a cell that contains the criteria instead of typing it directly.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.