Tag Archives: functions

Excel Tip of the Week: SUMIFS & COUNTIFS

Posted on April 20, 2012 by Doug Midkiff

Today’s topic is SUMIFS and COUNTIFS.

You might be thinking, “Wait a minute Doug, you already covered this topic!” But look again at those formula names, there is an “S” at the end of them indicating a completely different and more advanced function.

These two formulas are actually new to Excel as of 2007. These formulas have helped reduce the need for moving data into Microsoft Access or coming up with super complex array formulas. As their names might suggest these functions allow the user to sum or count data based on multiple criteria instead of just a single criteria.

Here are the inputs for the SUMIFS function:

Notice how “sum_range” is the first input in this for formula which is the opposite of the SUMIF function. Also notice the ellipsis at the end which indicates that more inputs can be received. This formula can actually handle up to 127 different range/criteria pairs.

Here are the inputs for COUNTIFS:

COUNTIFS is very similar to SUMIFS just without the “sum_range.” This formula can also hold up to 127 different range/criteria pairs.

Let’s look at an example. Download the example spreadsheet here.

Read the full post (est. reading time 1:20 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , , , , | 1 Comment

Excel Tip of the Week: RANK

Posted on February 24, 2012 by Doug Midkiff

Today’s topic is the RANK function.

RANK allows you to return the rank of a number among a specified range of numbers. RANK takes three inputs; two required and one optional:

Number is the single number you want ranked
Ref is the range of values you would like the above number to be ranked against
[order] is optional and is a “1” or “0” option. 0 = Descending and 1 = Ascending. If you leave it blank, Excel assumes “0″ and ranks the largest number first. If you type “1” then Excel will rank the largest number as last.

This function can come in handy when your data is not set up in a way where you can sort it to figure out the highest or lowest number. Also, it can help if you would like to rank a number from outside your main data set against your main data set. Let’s walkthrough a [very simple] example:

  1. Download the example spreadsheet here
  2. Move your cursor to cell C4
  3. Type in: =RANK(D4,$D$4:$D$16,0) **Note the absolute cell references**
  4. Copy the formula down
  5. That’s it. Good job.
Read the full post (est. reading time 1:30 mins)
Posted in Doug Midkiff '12, Excel Tip of the Week | Tagged , , | Leave a comment