How to count responses? Possibly use COUNTIF?
 

We have a survey that asks people how satisfied they were with our services.
It also asks which staff member assisted them. I would like to count up the
number of each level of satisfaction per person and put it in a table.
Example below.

A B
Lisa Very Satisfied
Brent Very Satisfied
George Dissatisfied
Lisa Satisfied
Brent Very Satisfied
Casey Neutral

So if I was counting from the above I would see:

VS S N D
Brent 2
Casey 1
George 1
Lisa 1 1

Any help would be great!

How to count responses? Possibly use COUNTIF?
 

This looks like a perfect time to learn about pivottables.

Add headers to your data if you don't have them, then you can select the range
(A1:B100??), data|pivottable

Follow the wizard until you get to the step with a Layout button on it.
Click that button.

Then drag the header for the name to the row field.
Then drag the header for the response to the Column field
Drag the header for the response to the data field.

Then finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

UWSPITHD wrote:

We have a survey that asks people how satisfied they were with our services.
It also asks which staff member assisted them. I would like to count up the
number of each level of satisfaction per person and put it in a table.
Example below.

A B
Lisa Very Satisfied
Brent Very Satisfied
George Dissatisfied
Lisa Satisfied
Brent Very Satisfied
Casey Neutral

So if I was counting from the above I would see:

VS S N D
Brent 2
Casey 1
George 1
Lisa 1 1

Any help would be great!

--

Dave Peterson

How to count responses? Possibly use COUNTIF?
 

If the cell with the "2" in your sample result is cell G2, for example (G1
has VS, F2 has Brent, etc), then put this formula in G2, fill down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Very Satisfied")
H2 & down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Satis fied")
I2 & down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Neutr al")
J2 & down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Dissa tisfied")

"UWSPITHD" wrote in message
...
We have a survey that asks people how satisfied they were with our
services.
It also asks which staff member assisted them. I would like to count up
the
number of each level of satisfaction per person and put it in a table.
Example below.

A B
Lisa Very Satisfied
Brent Very Satisfied
George Dissatisfied
Lisa Satisfied
Brent Very Satisfied
Casey Neutral

So if I was counting from the above I would see:

VS S N D
Brent 2
Casey 1
George 1
Lisa 1 1

Any help would be great!

How to count responses? Possibly use COUNTIF?
 

Thank you. I used your solution to get the counts for this solution.

"Bob Umlas" wrote:

If the cell with the "2" in your sample result is cell G2, for example (G1
has VS, F2 has Brent, etc), then put this formula in G2, fill down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Very Satisfied")
H2 & down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Satis fied")
I2 & down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Neutr al")
J2 & down:
=SUMPRODUCT(N($A$1:$A$100=F2),N($B$1:$B$100="Dissa tisfied")

"UWSPITHD" wrote in message
...
We have a survey that asks people how satisfied they were with our
services.
It also asks which staff member assisted them. I would like to count up
the
number of each level of satisfaction per person and put it in a table.
Example below.

A B
Lisa Very Satisfied
Brent Very Satisfied
George Dissatisfied
Lisa Satisfied
Brent Very Satisfied
Casey Neutral

So if I was counting from the above I would see:

VS S N D
Brent 2
Casey 1
George 1
Lisa 1 1

Any help would be great!

How to count responses? Possibly use COUNTIF?
 

Thank you for your answer. I did not use your solution directly for this
answer, but it did help me with another issue I was having in a different
spot of the document. Thank you again.

"Dave Peterson" wrote:

This looks like a perfect time to learn about pivottables.

Add headers to your data if you don't have them, then you can select the range
(A1:B100??), data|pivottable

Follow the wizard until you get to the step with a Layout button on it.
Click that button.

Then drag the header for the name to the row field.
Then drag the header for the response to the Column field
Drag the header for the response to the data field.

Then finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

UWSPITHD wrote:

We have a survey that asks people how satisfied they were with our services.
It also asks which staff member assisted them. I would like to count up the
number of each level of satisfaction per person and put it in a table.
Example below.

A B
Lisa Very Satisfied
Brent Very Satisfied
George Dissatisfied
Lisa Satisfied
Brent Very Satisfied
Casey Neutral

So if I was counting from the above I would see:

VS S N D
Brent 2
Casey 1
George 1
Lisa 1 1

Any help would be great!

--

Dave Peterson