PDA

View Full Version : Count in excel excluding repeating numbers


jayjo97
May 2, 2008, 09:12 AM
Is there a way for me to count in a column and exclude numbers that repeat? I am working a spreadsheet with account numbers and need to count how many account numbers... the problem is that some of them repeat within the spreadsheet. Can someone help?

Thanks in advance!

JBeaucaire
May 2, 2008, 02:38 PM
I don't know why this works, but I use it on one of my docs.

OK, first hightlight the entire range of cells that include the account numbers. I'm hoping they are all in a single column. With the cells highlighted, click on INSERT > NAME > DEFINE. In the popup that appears, type AccNums and click ADD.

You've just "named" all your account numbers and can use that name in a formula. Here is the formula:


=SUMPRODUCT((AccNums<>"")/COUNTIF(AccNums,AccNums&""))

This formula should result in the number of how many unique items it found in the range. It should also ignore blank cells.