You’ve got a whole list of information but what do you do with it?  You may want to know how many records you have of a particular type or the total or average of that set of data.  There are several different ways to get this information.  You could use subtotals, pivot tables, or the method we are going to use here which is using the functions COUNTIF, SUMIF and AVERAGEIF.  The advantage of using functions is that the answers can be positioned anywhere you like on your spreadsheet which is really useful if you want to create a summary page, for example.  Also, if the data changes the calculations automatically update.

COUNTIF

If you need to find the total number of people in each department or the number of times you have sold a particular product then this is the function you need.  It calculates the total number of entries of a particular type.

Structure

=COUNTIF(range, criteria)

The range is the range of cells you want to be evaluated and the criteria is the condition that defines the cells to be counted.

Example

=COUNTIF(C2:C28,”Credit”)

Counts the number of entries in cells C2 to C28 that contain the word Credit.

Image of countif function

SUMIF

So you have found the number of records of a particular type but what if you now need to find the total of this information.  For example, you have found the number of people in each department by using COUNTIF and now you need to find the total salary for them you would use SUMIF.

Structure

=SUMIF(range, criteria, sum range)

The first 2 parts of the function are the same as for the COUNTIF function. The sum range is the actual range of cells to sum, the range is the range of cells you want to be evaluated and the criteria is the condition that defines the cells to be counted.

Example

=SUMIF(C2:C28,”Credit”,F2:F28)

Finds the cells from C2 to C28 that say Credit then totals the corresponding numbers in cells F2 to F28.

Image of sumif function

AVERAGEIF

You have found the number of records and the total of the data but now you want to find the average instead.  For example, you need to find the average salary for the people in each department then you would use AVERAGEIF.

Structure

=AVERAGEIF(range, criteria, sum range)

The first 2 parts of the function are the same as for the COUNTIF and SUMIF functions already described. The average range is the actual range of cells to average, the range is the range of cells you want to be evaluated and the criteria is the condition that defines the cells to be counted.

Example

=AVERAGEIF(C2:C28,”Credit”,F2:F28)

Finds the cells from C2 to C28 that say Credit then averages the corresponding numbers in cells F2 to F28.

Image of averageif

NOTE: The criteria for all these functions can be entered in quotes as shown in the examples above or can be a cell containing the text in which case quotes are not required.

Want to learn more about Excel then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.

MEMBERSHIPS

FOLLOW LARA