Excel 2007 : Do a tally of ansers given to multiple users

[PACS Rob]

I have a spread sheet with one columb of people that were questioned. I need to know how maney time each person gave a particular response. (example)

Bob Yes
Steve No
Fred Other
Sam Yes
Bob Yes
Fred Other
Fred No

Need to solve
How maney times did Bob say "Yes"
How maney times did Bob say "No"
How maney times did Bob say "Other
How maney times did Steve Say "Yes" etc. etc.

I need a fraomula that will do the tally for me so that I can make a chart

[Søren Larsen]

Assuming your data is in columns A and B and starting in row 1, then this would count how many times Bob answers "Yes":

Please Login or Register  to view this content.

If you create a table with row/column headers with name/answer, then you can write one formula to paste in the table and give you all the answers.

[Richard Buttrey]

Since you don't have Excel 2007 or later you'll need a helper column

Assuming data is in A1:B7

C1 copied down
=A1&B1

Now create a unique list of names in say E1:E4 (You can use Data Filter Advanced Unique to do this) enter the following
F1:
=COUNTIF(C:C,E1&"Yes")
G1
=COUNTIF(C:C,E1&"No")
H1:
=COUNTIF(C:C,E1&"Other")

and copy F1:G1 down to F4

[Cutter]

You'll have to use SUMPRODUCT() if you want to do it by formula and not use helper column.

=SUMPRODUCT(($A$1:$A$100="Bob")*($B$1:$B$100="Yes"))

change the ranges to suit and use cell refs instead of the names and responses

---------- Post added at 03:16 PM ---------- Previous post was at 03:14 PM ----------

@ Soren

I don't think you can use whole column (or row) ranges for SUMPRODUCT() in 2003

[PACS Rob]

SUMPRODUCT((A:A="Bob")*(B:B="Yes"))

This was the perfect ansers, Thank you both very much for your input. I was surching all day for the formula

[Cutter]

Whole column ranges aren't supposed to be possible in 2003: http://www.excelhero.com/blog/2010/0...umproduct.html
See first paragraph under: "There are some points to note here."

Anyway, PACS Rob:

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save