Unable to understand the formula explained

[Dheeraj]

Hi All,


I am unable to understand the formula explained at the following link However, it is working fine:


http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/


Let me explain the problem:


1. Syntax for Countif is "=COUNTIF(range,criteria)" i.e. in the arguments, Range comes first then the criteria. Whereas in the in Step-1 (Last 2 lines) it is explained just the opposite.


2. Actually I am having problem understanding COUNTIF($B$1:B1,List) or COUNTIF(List,List).


I have basic knowledge of CountIf function but in many Array Formulas, COUNTIF(Range,Range) is used. I want to understand how it works when we use =countif(Range,Range)


Thank you,

Dheeraj

 

[Hui]

Dheeraj,


The easiest way to understand how a Countif(Range1, Range2) works is via example

look at the following data in A1:B4

[pre]

9	2
2	2
3	3
3	1

[/pre]
If we use =COUNTIF(A1:A4,B1:B4)

and instead of pressing Enter press F9

Excel will display ={1;1;2;0}

What this means is that:

The first 1 means that the value 2 from B1 occurs once in the Range1

The second 1 means that the value 2 from B2 occurs once in the Range1

The third number, 2 means that the value 3 from B3 occurs twice in the Range1

The forth number, 0 means that the value 1 from B4 occurs Zero times in the Range1


Now if you enter =COUNTIF(A1:A4,B1:B4) in a cell it displays 1, this is actually the first value in the array {1;1;2;0}

If you don't believe me select 4 cells like D1:D4

Enter =COUNTIF(A1:A4,B1:B4) and then press Ctrl Enter

Excel will display the values 1, 1, 2 0 in the 4 cells


But if you enter =Sum(COUNTIF(A1:A4,B1:B4)) and Ctrl Shift Enter

excel displays 4

Which is the sum of the array we saw above namely {1;1;2;0}


In the Formula Forensics Series I spend a lot of time explaining how Array Formulas work

You may want to have a read at : http://chandoo.org/wp/category/formula-forensics/


I hope that clears up your query a little bit

 

[Dheeraj]

Thank you so much Hui.


Now I have better understanding of CountIf.


Dheeraj