You are here: Home / Functions

Sum cells with multiple criteria in Excel 2007

August 25, 2009 By

In an earlier post I demonstrated How to sum cells with multiple criteria in Excel 2003. I used an Array Formula consisting of the SUM function and the IF function.

In Excel 2007 however a new function is introduced that facilitates sum with multiple criteria: the SUMIFS function.

Syntax

SUMIFS (sum_range, criteria_range1, criteria1, criteria range2, criteria2…) … up to 127 range/criteria can be specified.

sum_range : Required. One or more cells to sum.

criteria_range1: Required. The first range in which to evaluate the associated criteria.

criteria1: Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added.

criteria_range2, criteria2, … : Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

In the example below we want to sum the sales for “John” in quarter 2 (Q2). I.e. we have two criteria:

1. “John” in column B.
2. “Q2″ in column C.

We will use the following formula for this purpose:

=SUMIFS(D2:D17;B2:B17;”John”;C2:C17;”Q2″)

Only rows 3, 10 and 14 will qualify for this formula (yellow color) and it will give us a result of 9547 as you can see.

xl7sumifs


Filed Under: Excel 2007, Formulas, Functions Tagged With: , , ,

How to calculate a running average in a column

August 7, 2009 By

To calculate a running average, or a moving average, for a range of cells (a column), you use the AVERAGE function. The only trick you need to apply is to make your range changing continuously.

In the example below we want to calculate the running average of the sales from January to June.

Our ranges for the months will be as follows:

January – one cell : B2:B2
February- 2 cells B2:B3
March – 3 cell2: B2:B4 … and so on.

The first cell of the range is always the same for all months : B2, so we will make it absolute reference like this: $B$2. Therefore our formulas , in C column will be like this:

January: =AVERAGE($B$2:B2)
February: =AVERAGE($B$2:B3)
March: =AVERAGE($B$2:B4) …. Ans so on.

Enter the first formula in cell C2 and autofill down the rest of the range.

running-average1

Data

running-average2

Formulas

Filed Under: Data, Excel 2007, Formulas, Functions Tagged With: , , ,

Average cells based on multiple criteria in Excel 2007

July 9, 2009 By

To average cells based on multiple criteria in Excel 2007 you use the AVERAGEIFS function.

Syntax

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Average_range is the range of cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.

Example:

In this example we will average the total sales for Jim in quarter 2 (olive green rows). So our criteria are:

  1. “Jim” in column B.
  2. “Q2″ in column C.

Our formula will be like this:

=AVERAGEIFS(D2:D23;B2:B23;”Jim”;C2:C23;”Q2″)

This formula returns a value of 1094, which is (2540+1250+178+408)/4

xl7averageifs

Notes:

  1. If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
  2. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
  3. Each criteria_range must be the same size and shape as sum_range.
  4. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.

Filed Under: Data, Excel 2007, Formulas, Functions Tagged With: , ,

Average values based on a given criteria in Excel 2007

June 15, 2009 By

To average a range of values that meet a given criteria or condition, you use the AVERAGEIF function.

Syntax

AVERAGEIF(range,criteria,average_range)

Range : The group of cells to be evaluated for criteria.

Criteria: is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.

Average_range: is the actual set of cells to be averaged. If omitted, range is used.

Example:

In the example below we want to average the total sales for a particular salesman (Jim). So our range is B2:B13 and our criteria is “Jim”. The range to be averaged is C2:C13. The formula will be:

=AVERAGEIF(B2:B13;”Jim”;C2:C13)

The result is 1005, i.e. (2300 + 1100 + 200 + 420) / 4

xl7average1

Notes:

1. Cells in range that contain TRUE or FALSE are ignored.

2. If a cell in average_range is an empty cell, AVERAGEIF ignores it.

3. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.

4. If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.

5. If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.

6. You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. In the previous example if you use “J*” in criteria then both “Jim” and “John” will be included in the average.

Filed Under: Data, Excel 2007, Formulas, Functions Tagged With: , , , ,

How to generate a random number within a given range

May 24, 2009 By

To generate a random number within a given range use the RANDBETWEEN function.

Syntax

RANDBETWEEN(bottom,top)

Bottom: is the smallest integer RANDBETWEEN will return.

Top: is the largest integer RANDBETWEEN will return.

Examples:

RANDBETWEEN (1,100) will generate numbers from 1 to 100

RANDBETWEEN (-15,2000) will generate numbers from -15 1 to 2000

Notes:

1. A new random integer number is returned every time the worksheet is calculated.

2. In Excel 2007 you can use RANDBETWEEN function directly, however in Excel 2003 (and may be earlier versions also) the function is part of the Analysis ToolPak add-in. If it is not available in your list of functions then you have to install the Analysis ToolPak. Check the following link for more help:

install and load the Analysis ToolPak add-in in Excel 2003

Filed Under: Excel 2007, Functions Tagged With: , ,

Count cells with multiple criteria in Excel 2007

April 18, 2009 By

In an earlier post I demonstrated How to count cells with multiple criteria in Excel 2003. I used an Array Formula together with the SUM function.

Fortunately enough in Excel 20007 we do not need that workaround. Now there is a direct function that count cells with multiple criteria: the COUNTIFS function.

Syntax

COUNTIFS(range1, criteria1,range2, criteria2…) … up to 127 range/criteria can be specified.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2), with total sales that exceed 1000. i.e. we have three criteria:

  1. “John” in column B.
  2. “Q2″ in column C.
  3. “>1000″ in column D.

We will use the following formula for this purpose:

=COUNTIFS(B2:B17;”=John”;C2:C17;”=Q2″;D2:D17;”>1000″)

This formula will give us a result of 2 as you can see.

xl7countifs

Filed Under: Excel 2007, Formulas, Functions Tagged With: , ,

How to use the INDIRECT function

March 18, 2009 By

The INDIRECT function returns a reference specified by a text string, evaluates that reference and displays its contents. The function is useful when you want a cell reference in a formula to be variable, without the need to change the formula every time.

Syntax

INDIRECT(ref_text,a1)

ref_text: a text that contains a cell reference.
a1: a logical value that specifies what type of reference is contained in the cell ref_text.
• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. E.g. C12
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. E.g R12C3

Example 1:

indirect1

Cell B1 contains the formula: =INDIRECT(A1)
Cell A1 contains the text: “C6″
Cell C6 contains the value: 135
The INDIRECT function will evaluate the reference as: = C6, which is equal to 135.
If you change the text in cell A1 to “C3″ then you will get 198 in B1.

Example 2:

indirect2

The data shown above is a snapshot from a sheet named “Sheet2″ in a workbook.

Cell B1 contains the formula: =INDIRECT(A1&”!”&”C8″)
Cell A1 contains the text: “Sheet2″
Cell C8 contains the value: 1922
The INDIRECT function will evaluate the reference as: = Sheet2!C8, which is equal to 1922.
If you change the text in cell A1 to “Sheet1″ then the reference will be evaluated as: = Sheet1!C8 and you will get whatever value in Cell C8 of sheet1.

Filed Under: Formulas, Functions Tagged With: ,

How to use the conditional sum wizard

March 17, 2009 By

The Conditional sum wizard helps you create complex formulas to sum values in a column based on certain criteria. The wizard is part of Excel Add-ins. You must load it first before it can show up in the Tools menu. See then bottom of this post if you want to know how to load the wizard.

The following example will demonstrate how to use the conditional sum wizard:

Suppose we have data of total sales by month and product as shown below, and we want to sum the total sales for Product3 in the first quarter (months 1 to 3).

1. On the Tools menu click Conditional Sum. The dialogue for step1 will open.

csumwizard1

2. Click on the text box then select your data range, including column headings.
3. Click next. The dialogue for step2 will open.

csumwizard2

4. Specify your conditions by manipulating the three combo boxes for Column, Is and This value. Click Add condition every time.

5. When finished with your conditions click next. The dialogue for step 3 will open.

csumwizard3

6. Specify whether you want to copy your generated formula only, or both formula and the values of your conditions.

7. Click next. The dialogue for step 4 will open.

csumwizard4

8. Specify the cell(s) where you want your generated formula (and values of conditions if applicable) to be copied.

9. Click finish. The formula will be copied to the specified cell, and the result of the formula will be shown. Here is the formula in our example:

{=SUM(IF($A$2:$A$19<4,IF($B$2:$B$19=”Product3″,$C$2:$C$19,0),0))}

How to Load the Conditional Sum Wizard

The Conditional Sum Wizard is not loaded by default. To determine whether it has been loaded, on the Tools menu, look for the Wizard menu item. If it is not present, or if when you point to it, the item Conditional Sum is not present, you need to load the add-in.

To load the Conditional Sum Wizard, follow these steps:

1. On the Tools menu, click Add-ins.
2. In the Add-Ins available list, select Conditional Sum Wizard, and then click OK.

Applies to Excel 2003

Filed Under: Formulas, Functions Tagged With: , ,

How to use the RANK function

March 13, 2009 By

The RANK function returns the rank of a number, i.e. its position within a list of numbers. It is based on the value of a number, relative to the other numbers in the list.
The list evaluated by the rank doesn’t have to be sorted, but to get the idea of how items are ranked imagine that the list is sorted, and then each item is given a rank depending on its position in the list.

Syntax

RANK(number,ref,order)

Number:  is the number whose rank you want to find.

Ref:  is an array of or a reference to a list of numbers. Non-numeric values in the array or the  reference are ignored.

Order: is a number specifying how to rank number.

• If order is 0 (zero) or omitted, Excel ranks number as if the list is sorted in descending order.
• If order is any nonzero value, Excel ranks number as if the list is sorted in ascending order.

Note:

The RANK function gives duplicate numbers the same rank. This however will affect the ranks of subsequent numbers. For example, if we have two numbers both have the rank of 5, then no number will have a rank of 6. The next number in the list will have a rank of 7.

Examples:

1. Rank with order 0 (descending).

rank1

This example shows ranks in descending order (Order = zero). Here are the applicable formulas in each cell:

C2: =RANK(B2,$B$2:$B$6,0)
C3: =RANK(B3,$B$2:$B$6,0)
C4: =RANK(B4,$B$2:$B$6,0)
C5: =RANK(B5,$B$2:$B$6,0)
C6: =RANK(B6,$B$2:$B$6,0)

2. Duplicate ranks.

rank2

The second example shows duplicate ranks, also in descending order (Order = zero). Notice that students 2 and 3 both have a rank of 2, and therefore rank 3 is skipped. Here are the applicable formulas in each cell:

C12:RANK(B12,$B$12:$B$16,0)
C13:=RANK(B13,$B$12:$B$16,0)
C14:=RANK(B14,$B$12:$B$16,0)
C15:=RANK(B15,$B$12:$B$16,0)
C16:=RANK(B16,$B$12:$B$16,0)

Applies to: Excel 2003

Filed Under: Formulas, Functions Tagged With: ,

How to extract text from another text string

February 1, 2009 By

Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation.

Example:

Suppose that cell A1 has the text string:

“Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function:

=LEFT(A1,5)

The result is “Creat”

2. To get the last 11 characters of the string, we use the RIGHT function:
=RIGHT(A1,11)

The result is “certainties”

3. To get 7 characters from the string starting with position 10, we use the MID function:

=MID(A1,10,7)

The result is “y requi”.

4.To get the first word of the string, we use the LEFT and FIND functions:

=LEFT(A1,(FIND(” “,A1)-1))

The result is “Creativity”.

The FIND function here will give us the position of the first SPACE. We subtract 1 to get the length of the first word. That length is used as a parameter for the LEFT function.

Filed Under: Formulas, Functions Tagged With: , , , ,
« Older Posts
Newer Posts »