=countif($b$2:b2,b2)

**xtinct2** · 12-09-2011, 10:42 AM

Hi,

I use this formula, =COUNTIF($B$2:B2,B2), to count the number of duplicates in a sorted column and it works well (the first row with the value returns a 1; if the next row has the same B value as the previous, it returns a 2; and if the 3rd row does as well, a 3...and so on). It gives me the information I need, but my supervisor is asking me to explain it to her because she hasn't seen it before and she doesn't completely trust it yet.

The problem:
Someone told it to me and I never asked for an explanation, I just accepted it. So I don't fully understand it either. But I can't ask her to "just accept it" like I did.
Can someone please break down the formula for me so I can explain it to my supervisor?

thank you,
kb

**NBVC** · 12-09-2011, 10:57 AM

The $B$2 fixes the start of the range to B2. As you copy formula down you are always counting starting from B2, and the bottom of the range increases as you copy down, so the range gets bigger by one cell as you copy down, you are therefore counting how many times the value in B of the current row you are in appears from the start B2 to the current row.

Have a look at COUNTIF function in Excel help for details on how the function works in general.

**Fotis1991** · 12-09-2011, 11:04 AM

Hi xtinct2

countif($b$2:b2,b2)

Locking($) the first row of your list. This is the secret...!

So if you see next row, the formula is:=COUNTIF($B$2:B3;B3)

So B2 is not chances and when you drag down the formula, in every row saws you; how many times this number(in column B), appears in your list(until this row)

Hope to helps you.

Thread: =countif($b$2:b2,b2)

Thread Tools

Display

=countif($b$2:b2,b2)

Re: =countif($b$2:b2,b2)

Re: =countif($b$2:b2,b2)

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions