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: –

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

**3 things we want to know: –**

- No. of orders received for Tables (no. of orders only, not quantity).
- Total of quantity of Tables ordered in all.
- Total Sale value of Tables in all.

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)**