Ask Experts Questions for FREE Help !
Ask
    jayjo97's Avatar
    jayjo97 Posts: 1, Reputation: 1
    New Member
    		  
    #1

    May 2, 2008, 09:12 AM
    Count in excel excluding repeating numbers
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    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:
    Code:
    =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.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel count function [ 12 Answers ]

I am trying to count a large group of cells that are colored depending on the data. For example: I want to count the number of red cells which are faults. I tried the countif but not sure how to set the criteria. Thanks for any help.

How to Count Differences in Excel [ 2 Answers ]

I have to believe this is possible, but I'm stumped. If I have a list: Dog Dog Cat Cat Hamster Dog Cat Bird

Padding numbers in excel [ 2 Answers ]

I have about 500,000 cells on a spreadsheet that all follow a letter and number pattern. There are two patterns that look like this: y124f07 and h67g02. So the first is: Letter, number, number, number, letter, number, number The second is: Letter, number, number, letter, number, number

How could I put 2 conditions with COUNT IF Function and NESTED IF in Excel model [ 6 Answers ]

Hi, I am a new member, I hope you could help me to complete my assignment tonight, I am stock with "Count IF" function in Microsoft Excel 2003 How could I write the function with this commands: Count if the Tax which is (E15) not equal to 0, then I want to use a nested if with these...

Excel Count Function [ 4 Answers ]

In November 2004 Leif 5233 wrote the following regarding counting cells highlighted in a certain colour: ------------------ Create this function: Function CountCol(SumRange As Range, intColor As Integer) As Integer Dim I As Integer Dim Cell As Range Set SumRange =...

View more questions Search