Sponsored Link

There was an error in this gadget

Sponsored Link

Wednesday, March 18, 2009

Countif function

This function works like SUMIF function. Like in last post I am using the same example. As the name suggest, COUNTIF finds the count based on criteria provided by user. Very useful when you are summarizing data or excluding unwanted  by setting criteria. Also, in forthcoming post I will provide examples of COUNTIF function that too in complex formula's. I will summarize data from baby product sales using Countif. 

Formula: =COUNTIF(RangeOfThingsToBeCounted ,CriteriaToBeMatched)

Here is example below which show day-wise baby product sales.

Date Item Quantity Cost Amount
17-Mar-09 Dolls 44 148 6512
12-Jan-09 Hairpin 8 57 456
15-Apr-08 Diapers 8 176 1408
28-Jun-08 Baby Oil 29 124 3596
24-Nov-09 Dolls 43 120 5160
16-Oct-09 Diapers 34 159 5406
24-Mar-09 Baby Oil 39 196 7644
16-Jan-09 Diapers 26 154 4004
17-Apr-08 Dolls 28 84 2352
4-Jul-08 Baby Oil 39 82 3198
28-Nov-09 Diapers 36 101 3636
20-Oct-09 Cap 30 117 3510
3-Apr-09 Dolls 17 122 2074
23-Jan-09 Cap 45 55 2475
25-Apr-08 Dolls 6 93 558
        51989

The task here is to find count of each product in summary table based on Item name. So item name is criteria here. 

Table below shows part one where we want to summarize the sales table item-wise.

Range part of COUNTIF covers B2:B16 examines which cells has item name, B22 has Doll which it takes as Criteria can only count where it finds respective items.

Items Count Formula
Dolls

5

=COUNTIF(B2:B16,B22)
Hairpin

1

=COUNTIF(B3:B17,B23)
Diapers

4

=COUNTIF(B4:B18,B24)
Baby Oil

3

=COUNTIF(B5:B19,B25)
Cap

2

=COUNTIF(B6:B20,B26)

Another examples below demonstrates find count of items where count if greater than 25.

Criteria Total Formula
Count where Quatity >25

11

=COUNTIF(C2:C16,">25")

Range part of COUNIF covers C2:C16 examines quantity column which cells has number greater than 25. Here Criteria is a string ">25".

I am sure this post is helpful in understanding COUNTIF function. Post your comments

I am sure after SUMIF in the last post, you will find COUNTIF easy. Enclosed is link to download the COUNTIF Example

COUNTIF Example download

If you are first time visitor, please subscribe via email to receive updates, add-ins, e-books and lot more.

We assure you knowledge, not SPAM!

0 comments:

Post a Comment