Transcripts for the above video clip:

SUMIF and COUNTIF

The SUMIF function allows you to sum a selection of cells depending on certain criteria and the COUNTIF function allows you to count the number of cells that meet a certain criteria.

In this segment you will learn how to activate these functions, and use these functions to add or count cells based on set criteria.

In this example we have a list of Sales People, the Departments they’re in and the Units and Total sales. What we’d like to do is Summarise by Department, the Number of Sales people in that department. So we need to count the number of sales people and then the total units sold and total sales by summing the individual sales people information.

If we want to count the number of sales people in each department we can make use of Excels COUNTIF function,

• click on the cell where you want the counting to be done
• activate the Function Wizard
• and find the COUNTIF command,
• click Ok
• what it is asking for is the Range, that is the range of cells from which you want to count,
• and in this case we want to highlight the Department
• and it’d be useful to freeze those cells
• and the Criteria what its asking is, for this cell what criteria will result in me counting it,
• and in this case what we want to do is refer to the Department Name,
• so we’ll click in this cell
• and if we say Ok,
• you’ll see it has gone through this list,
• counted the number of times A appears,
• and listed that number
• and we can now copy that down,
• and what it’ll do is in this cell it will look for all the B’s, identify the B’s
• count them
• and put that number in,

Similarly if you want Excel to work through a list and based on certain criteria, add up certain information you can use the SUMIF function,

• you click in the cell,
• you activate the Function Wizard
• and you find the SUMIF function,
• you say Ok,
• what it is asking for is the Range which determines the criteria,
• so what we’re going to do is we’re going to highlight that section
• and we’re going to make it absolute,
• the Criteria we want to use is the Department Name so were going to click over here
• and the Sum range is what cells do you want added up if the criteria is met
• and in this case we want the Units Sold so we’re going to highlight that
• and also make it absolute
• and when we click Ok
• you’ll see that Excel has worked through the list, found all the A’s and added up their Units Sold
• and if we copied it down
• you’ll see that it does a similar thing for all the B’s
• and all the C’s
• to give you a total which is the same as the original list.

You can do the same thing for Total Sales,

• click on the cell,
• activate the wizard
• find the SUMIF function,
• specify the Range,
• make it absolute if you need to
• the Criteria is the Department Name
• and the Sum range in this case will be the Total Sales,
• make it absolute again,
• and when you click Ok
• and copy it down
• you’ll get a breakdown of the Total Sales by department
• and you can now maybe work out averages of Total Sales by the Total number of Sales People.