Need help with formula

[Guest]

Hi!
I need to create a formula that will count unique
(duplicating/repeating)values. I have numeric data in one
column and text data in another.I want to create a
formula that will look at a value in column b that will
correspond with a value in column a, then give a count on
the duplicate/repeating value. I was going to use a
sumproduct formula, but there are over 3700 unique
values. Here is an example of my data.
Looking at the data below column b at Jdoe, and count how
many time 123 appeared next to Jdoe in column A, then
return the result in C1.
The formula I was going to use =sumproduct((A1:A12000=123)
*(B1:B12000="jdoe")). This would have to be created over
3700 times to get the correct result. This needs to be
created for all names in column B, which could correspond
to several different values in column a. There has to be
a quicker way.
Column A Column B Column C
A1:A12000 B1:B12000
123 Jdoe 2
456 Rsmith
789 Bmiller
987 Jdoe
654 Sjones
321 Bmiller
123 Jdoe
789 mbutler

Thanks T

 

[Jason Morin]

Here's what I'd do:

1. Ensure col. A and col. B have headers.
2. Select col. A and col. B
3. Go to Data > Pivot Table and PivotChart Report.
4. Hit Next twice.
5. Click the Layout button.
6. Drag the number header to the ROW area, then drag the
name header underneath it, then drag the number header
again to the DATA area.
7. Hit OK then Finish.
8. Right-click on one of the total rows and select Hide.
9. Select columns A thru C.
10. Edit > Copy and then Edit > Copy > Paste Special >
Value.
11. Select the range in col. A then begins with the first
name (probably cell A5) and ends with the last name.
12. Press F5 > Special > Blanks > OK.
13. Press "=" and click the first name.
14. Press <ctrl><enter>.
15. Now use your SUMPRODUCT function and drag down.
Here's what mine looked like:

=SUMPRODUCT((A5=Sheet1!$B$2:$B$9)*(B5=Sheet1!$A$2:$A$9))

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi
I would use a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm