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!