Solving practical problems with Excel conditional functions

Autumn to the winter solstice, and to the end of a year. In the work, the data calculation and summary of the operation of a lot more than usual. As we all know, in Excel can carry on the different computation and the summary to the data, today I introduce to you to the condition related function three brothers, respectively is "COUNTIF", "SUMIF" and "IF" function. They have a common feature--the surname character "IF".

Big Brother: countif function (count sum)

The Count function, as the name suggests, is used to count the number of numeric cells in the selected range. COUNTIF is the extension and extension of the count function, adding the previous condition to the count, and counting only when the count is met. For example, from the Employee Information table, figure out how many people are older than 35 years of age.

Let's look at a typical example of a category Count rollup. Here is a list of sales records, how many "sales orders" have been made by each salesperson?

Big Brother Countif. It takes two parameters-the condition area (this example is the Salesperson column in the left table) and the count condition (this example is the name of the person in the table on the right). To calculate the number of orders for the first salesperson, it's easy to enter the function formula "=countif ($C $: $C $16,e2)" (see Figure 1).

Two brothers: sumif function (conditional sum)

The SUM function is the sum of the data, and the SUMIF extends and expands it, such as calculating the sum of the data in the amount of 1 yuan, the sum of the data by the person or product category, and so on. It has 3 parameters, namely the condition region, the judgment condition, the actual summation region (if it is an area with the "conditional region"), it can be omitted.

In the example above, the total order amount for each salesperson is calculated by using the SUMIF function to assist. If you want to calculate the total amount for each person's sales order, consider the "Salesperson" column on the left-hand table as a "conditional area", and treat each list in the right-hand table as a summation "condition", with each "Order Amount" in the table on the left as the "actual summation area", and enter the data calculation formula in the G2 cell =sumif ($C $2:$ C$16,e2, $B $: $B $16) "(see Figure 2), the 1th salesperson's" total order "was instantly generated.

Small tip:

In the COUNTIF and SUMIF functions of this example, because the salesperson area is fixed with the total order area, you use absolute address when referencing the two column addresses in the function, that is, add the "$" symbol before the address.