COUNT WITH MULTIPLE CRITERIA

[Guest]

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

 

[T. Valko]

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

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

pivot table is too advanced for me. If you have the patience to help me with
this; please do. Thanks!

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

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

 

[T. Valko]

Try this:

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

Biff

 

[Guest]

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

pivot table is too advanced for me. If you have the patience to help me with
this; please do. Thanks!

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

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

 

[Guest]

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!

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

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

 

[Epinn]

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

pivot table is too advanced for me. If you have the patience to help me with
this; please do. Thanks!

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

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