Although I am a "fan" of SUMPRODUCT, I like PivotTables too. Sometimes PivotTables or Subtotals may be more efficient than SUMPRODUCT when one has a large spreadsheet. I say this based on experts' advice and not on my personal experience as I haven't dealt with a large spreadsheet. Experts, please feel free to correct me.

It is always good to hear an expert (in this case, Ron) who is conversant with advanced formulae to recommend PivotTables.

http://www.contextures.com/tiptech.html Scroll down to "P."

http://www.datapigtechnologies.com/ExcelMain.htm

This is my humble opinion.

Epinn

"Greg C" wrote in message ...
pivot table is too advanced for me. If you have the patience to help me with
this; please do. Thanks!

"Ron Coderre" wrote:

This might be a good situation to use a Pivot Table

You'd need to have column headings above your data.
I'll use "REF" and "NAME"

<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

COLUMN: Drage the NAME field here
ROW: Drag the REF field here
DATA: Drag the REF field here, again
If it doesn't list as Count of REF...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each NAME across the top,
each REF down the left
and the count of each REF for each NAME.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Greg C" wrote:

I would like to have a summary table set up like this:
A B C D
# Johnson Smith Jones
9904
9354

and have the cell calculate based on column A and Row 1 matches what the
count is. ANy help? Table Example below.

A B
9904 Johnson
9354 Smith
9714 Jones
9904 Smith
8151 Jones
8154 Johnson
9904 Johnson