CountIF and SumIF are amazingly useful functions for simple yet very useful calculations.

Let us take an example of you running a furniture shop.

In a particular month, you have sold a lot of products, order details as below: –

Order Details

Order Details

I have highlighted “Tables” on purpose that we will do some calculations on Tables’ orders.

3 things we want to know: –

  1. No. of orders received for Tables (no. of orders only, not quantity).
  2. Total of quantity of Tables ordered in all.
  3. Total Sale value of Tables in all.
CountIF & SumIF

CountIF & SumIF

Let’s solve each: –

1. No. of orders received for Tables (no. of orders only, not quantity).

We will use =CountIF for this.

=CountIF needs 2 inputs, Range and Criteria.

Here, the Range will the Product column, and the Criteria will be “Table” in double-quotes.

=CountIF(A5:A11, “Table”)

Thus, Excel looks into A5:A11 and counts how many times “Table” is there, and gives you the Final count.

2. Total of quantity of Tables ordered in all.

We will use =SumIF for this.

=SumIF needs 3 inputs, Range, Criteria and Sum_Range.

The Range and Criteria be same as above, but the Sum_Range will be Quantity column.

=SumIF(A5:A11, “Table”, B5:B11)

Thus, Excel looks into A5:A11 and finds which all cells have “Table”, and then goes to the Quantity column and checks the respective quantities for each entry. Then finally it adds up all the Quantities and gives you the Final sum.

3. Total Sale value of Tables in all.

We will use =SumIF for this.

The Range and Criteria be same as above, but the Sum_Range will be Total column.

=SumIF(A5:A11, “Table”, D5:D11)