How to Use SUMIF Conditional SUM Function in Excel
Microsoft Excel SUM Functions:
- SUMSQ – Square and Add Cell Values
- SUMPRODUCT – Multiply and Add Two Arrays
- SUMIF – Add with a Condition
- SUMIFS – Add with Multiple Conditions
- SUMX2PY2 – Add Sums of Squares of Two Arrays
- SMX2MY2 – Add Differences of Squares of Two Arrays
SUMIF(), Conditional Sum, is a wonderful function that allows you to find the sum of only selected cells in a table.
For example, let’s say you have a table of fish sales by quarters, fish types, regions, etc.
What if you wanted to find the sum of sales for the Atlantic and Pacific regions separately? SUMIF() function is ideal for that kind of calculations.
General formula is:
=SUMIF(range,criteria,[sum_range])
range is the range where the CONDITION is to be found. criteria is the cell that defines the CONDITION. sum_range is the range of the values that you’d like to add. For example, for ATLANTIC fish sales:
=SUMIF(A2:A33,A17,D2:D33)
For PACIFIC fish sales:
=SUMIF(A2:A33,A18,D2:D33)
And here are the results for conditional sums for TYPES OF FISHES:
MORE INFO
COMMENT by THOMAS SEELEY: “For the Criteria parameter, you should use the cells in columns H and I, not from column A in the data set because column A could become unsorted whereas the values in columns H and I should remain constant.”
