A revised version of this content is now available at blog.officewizkid.com
Here is another classic situation: I have an Excel file with two data column. First column contains list of numeric values, and the second column contains labels. Now I would like to sum all the cells in the first column that their second column has the value “Yes”. Something like in the screen below:
Excel has a beautiful function to do just that. It is called SUMIF. The function accepts three parameters:
- Range: Collection of cells to be used for comparison against the criteria. In our case it will be B:B.
- Criteria: A string representing the criteria. As with other functions like COUNTIF, the criteria can be simple as static value or a more complex such as wildcard for string comparison or numerical comparison operators such as “>” or “<”.
- Second range for sum: This is an optional parameter and represents the collection of cells that will be conditionally summed. In our simple example it will be A:A.
And the value in the above example would have been 4 (adding cell A2 and A4). Simple yet surprisingly very useful.
You can find more information on the criteria part of SUMIF at the following place: