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

Try this:

Your raw data list is in Sheet2

Enter this formula in B2 of your summary sheet:

=SUMPRODUCT(--(Sheet2!$A$1:$A$7=$A2),--(Sheet2!$B$1:$B$7=B$1))

Copy across then down as needed.

Biff

"Greg C" <Greg wrote in message
...
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

OK that works, but it appears the numbers that are listed in column A are not
numbers and reformatting is not helping. When I re-type the number in column
A it populates the value on the summary. This sheet has 4000 rows, so it's
not practical to retype the values. Any advice?

Thanks for your help!

"T. Valko" wrote:

Try this:

Your raw data list is in Sheet2

Enter this formula in B2 of your summary sheet:

=SUMPRODUCT(--(Sheet2!$A$1:$A$7=$A2),--(Sheet2!$B$1:$B$7=B$1))

Copy across then down as needed.

Biff

"Greg C" <Greg wrote in message
...
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

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

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

Try this:

Select an empty cell on your raw data sheet.
Copy that empty cell: EditCopy
Now, select the range of "numbers" in column A.
Then: EditPaste SpecialAddOK
This should change the format of column A to GENERAL and convert the "text
numbers" back to numeric numbers.

Biff

"Greg C" wrote in message
...
OK that works, but it appears the numbers that are listed in column A are
not
numbers and reformatting is not helping. When I re-type the number in
column
A it populates the value on the summary. This sheet has 4000 rows, so
it's
not practical to retype the values. Any advice?

Thanks for your help!

"T. Valko" wrote:

Try this:

Your raw data list is in Sheet2

Enter this formula in B2 of your summary sheet:

=SUMPRODUCT(--(Sheet2!$A$1:$A$7=$A2),--(Sheet2!$B$1:$B$7=B$1))

Copy across then down as needed.

Biff

"Greg C" <Greg wrote in message
...
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

OK....but, it's a lot easier than you think....

With
A1:B8 containing this list
Ref Name
9904 Johnson
9354 Smith
9714 Jones
9904 Smith
8151 Jones
8154 Johnson
9904 Johnson

Then....
From the Excel Main Menu
<Data<Pivot Table
Use: Excel........Click the [Next] button
Select your data (A1:B8).....Click the [Next] button
(or select a bigger range if you expect more data eventually...like A1:B100)

Click the [Layout] button to see the setup screen

COLUMN: Drag 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]

Check: Existing Worksheet....and select a cell in the sheet
(that cell will be the upper left corner of the pivot table)
Click the [Finish] button

The end result will look something like this(if the display formatting is
correct):
Ref Johnson Jones Smith Total
8151 1 1
8154 1 1
9354 1 1
9714 1 1
9904 2 1 3
Total 3 2 2 7

Does that help?

Post back with more questions.
***********
Regards,
Ron

XL2002, WinXP


"Greg C" wrote:

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

At first blush the pivot table appears to work. I will double check and ask
follow up questions if needed. Thanks for all your help!

"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

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