6.3 Conditional Functions

Last edited 477 days ago by Makiel [Muh-Keel].

CountIf

The COUNTIF function allows you to determine the number of cells within a range of cells that contain a specific value.

The first argument of the COUNTIF function is the range of cells within which you want Excel to count the number of matches. The second argument defines the specific value that you would like Excel to match to the cells in the range.

⁠

SumIf

The SUMIF function is used to calculate the total for a set of values that match a specific criterion.

⁠

AverageIf

The AVERAGEIF function is similar to the SUMIF function. The AVERAGEIF function is used to calculate the average for a set of values that match a specific criterion.

⁠

Here's the breakdown of this function's arguments:

$A$2:$A$20: This is the range where AVERAGEIF will look for the criteria. The dollar signs mean it's an absolute reference; the range will stay constant if you copy or fill the formula down to other cells.

D3: This is the criteria that AVERAGEIF is testing against the cells in $A$2:$A$20. In your example, D3 contains the text "White", so AVERAGEIF will look for cells in range $A$2:$A$20 that contain the text "White".

$B$2:$B$20: This is the actual set of cells AVERAGEIF will average if the corresponding cells in the range meet the criteria. So it will only average the values from $B$2:$B$20 where the corresponding cell in $A$2:$A$20 is "White".

The function in E3 will calculate the average value of all "White" cars based on the data in columns A and B. If you filled this formula down to E4, E5, and E6, it would calculate the average values for "Black", "Red", and "Blue" cars, respectively, assuming those colors are entered into D4, D5, and D6.

CountIf

SumIf

AverageIf

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.