#174: Excel – The Power of ‘IF’

Among the first few Excel functions most of us learn are the SUM and COUNT functions. This week I’d like to take a look at a couple of simple and very useful variations of those — SUMIF and COUNTIF.

Over on the right is a typical set of spreadsheet data which has been totalled using the SUM function. Piece of cake right? Now how would you get a total for each of the regions? You could manually create formulas pointing to the correct cells – but what if you have hundreds or even thousands of rows? Way too slow!

This kind of scenario is when it pays to know about the SUMIF function can save the day.  In this example, SUMIF can look for a specifi value in
column A and then add only the corresponding values in column B.

For example, let’s take a look at using SUMIF to find only the sum of the sales in the East region.

Syntax: =SUMIF(range, criteria, sum range)

Result = $16,257

Now, let’s see how COUNTIF can tell us how many entries there are for the East region.

Syntax:  =COUNTIF(range, criteria)

Result = 3

Now you know the ‘Power of IF’.  What do you think?

Advertisements

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