Excel’s SUMIF function

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’s SUMIF function

Excel has a beautiful function to do just that. It is called SUMIF. The function accepts three parameters:

  1. Range: Collection of cells to be used for comparison against the criteria. In our case it will be B:B.
  2. 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 “<”.
  3. 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.

=SUMIF(B:B,”Yes”, 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:

http://office.microsoft.com/en-us/excel-help/sumif-HP005209292.aspx

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

One thought on “Excel’s SUMIF function”

  1. Great example. I have found SUMIF invaluable in creating financial summaries and financial statements through the years. I like how Excel 2010 has given us the SUMIFS formula for using multiple criteria too. I’ve only recently decided to start sharing my experiences with Excel via the internet. Always something new to learn with this program.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s