Count only duplicates

Wskip49 · #1 09-07-2008, 06:47 PM

I would like to be able to count only the duplicate numbers without having to put the actual number in the formula.

123456
56478
951753
7727969
08259186
10741916
10741916
123456
123456

=countif(duplicate)

**Ron Coderre** · #2 09-07-2008, 06:53 PM

With
A1:A9 containing your posted list:
123456
56478
951753
7727969
8259186
10741916
10741916
123456
123456

This formula returns the count of values that are duplicated in that list:

Code:

B1: =SUMPRODUCT(--(COUNTIF(A1:A9,A1:A9)>1))

With that data, the formula returns: 5

Code:

123456----3 instances
10741916--2 instances

Is that what you're looking for?

Wskip49 · #3 09-07-2008, 07:19 PM

That works great however, if there is a formula in the cell it counts it. Is there a way to only count the answer to the formula?

**shg** · #4 09-07-2008, 07:22 PM

... Pardon?

Wskip49 · #5 09-07-2008, 07:32 PM

I have this formula in $A$2:$A$12. Using the formula you gave, Thank you, it counts the formula in each of the cells as duplicates.

This is the formula I have in each cell.

Code:

=IF(ISERROR(INDEX(Stu_ID,MATCH(F3,Names,0))),"",INDEX(Stu_ID,MATCH(F3,Names,0)))

**shg** · #6 09-07-2008, 07:56 PM

It was Ron's formula.

It counts the duplicates in the results of the formulas, not duplicate formulas.

Wskip49 · #7 09-07-2008, 10:05 PM

I don't have a zip file and I can't get the file downloaded. Thanks so much for you help.

**shg** · #8 09-07-2008, 10:28 PM

If you're running XP, just right-click the file and select Send to compressed folder

Wskip49 · #9 09-08-2008, 10:26 AM

Thanks for the tip on Compressing the file. I have attached.

**shg** · #10 09-08-2008, 11:44 AM

I would define

StuNo Refers to: =OFFSET(School!$A$1, 1, 0, COUNT(School!$A$1:$A$13))

Then,

Total Referrrals =COUNT(StuNo)

(assumes that student numbers are indeed numbers, and no interspersed blank rows between student numbers)

# Students Involved =SUMPRODUCT( 1 / COUNTIF(StuNo, StuNo) )

Wskip49 · #11 09-08-2008, 12:08 PM

This formula is just the ticket.

Quote:

=count(stu_id)

This formula give me a #DIV/0!.

Code:

=SUMPRODUCT( 1 / COUNTIF(Stu_ID, Stu_ID) )

**shg** · #12 09-08-2008, 01:29 PM

I don't know how you've defined Stu_ID.

Wskip49 · #13 09-08-2008, 02:25 PM

I named "stu_id" from another file ('[Student Info update.xls]Sheet1'!$A:$A) which contains about 650, 8 digit numbers, this is the formate I used, Custom 0#######.

**shg** · #14 09-08-2008, 02:34 PM

=SUMPRODUCT( 1 / COUNTIF(Stu_ID, Stu_ID) )

... gives the number of unique values in a list that may contain duplicates. Why would you apply it to the master list, which should not include any duplicates? In that event, it should give the same result as COUNT (if they are all indeed numbers), but it's needlessly demanding.

Wskip49 · #15 09-08-2008, 02:51 PM

I have the student number indexed and matched to the student name. I type the student name and his Student Number, Grade Level, Home Room, Ethnicity, *** and if he/she has special needs. I hope that explains. I hate to keep bothering everyone with these simple problems. I am trying to learn as much as I can.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How To Use The Count Function Like This?	atech	Excel Programming	1	06-25-2008 12:36 AM
Find and count Duplicates	flds	Excel General	2	06-19-2008 10:55 PM
Duplicates! Count, Remove, Highight, etc...	ImpetuousRacer	Excel Programming	4	11-21-2007 03:02 PM
How to use the count function?	atech	Excel Programming	2	09-21-2007 02:01 AM
How do i count duplicates and produce a sumary?	xyz123	Excel Worksheet Functions	3	05-31-2007 10:21 AM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)