HELP needed counting cells

D

DJ

I have a sheet with multiple columns with ouput from survey; I need to count
how many people have said 'yes', 'no' etc to a particular question (which I
have done) but also need to know by country i.e. 10% of UK people said 'no' -
so how do I cross-ref the two columns to get a total? HELP!!
 
E

EvilTony

Let us assume that your two UK columns are D and E and the cells range from 3
to 20

the formula for the cell that will hold the 'yes' totals is then

=countif(D3:E20,"yes")
 
D

DJ

Thks EvilTony,
Problem is that one column has yes, no - the other has UK, US etc etc - so
need to know how many in UK said yes, no - how many in US said yes, no - do
you see the problem?
 
C

CVinje

Can you provide us with an example of how your spreadsheet is setup? Like
what is in column A,B,C, etc and row 1,2,3,etc? This along with the formulas
you are using now might help.....

CVinje
 
J

Jarek Kujawa

Excel 2003

if you have a list of unique countries in D1:Z1 try

in D2
=SUMPRODUCT($A$1:$A$100=D1)*($B$1:$B$100="yes"))
in D3
=SUMPRODUCT($A$1:$A$100=D1)*($B$1:$B$100="no"))

then copy

if you do not have a list of countries try to create one with
selecting a column with countries then Data->Filter->Advanced Filter-
remember to tick "Unique records only"
Click to expand...

then copy the list to D1:Z1...

pls click YES if this post helped
 
D

DJ

Column A is yes/no; B is location - UK etc. Then the lines below are
responses to those. So far i have had to sort based on location (so all UK
together etc) and then did a countif. So for example if rows 1-50 were UK I
did a countif to see how many said YES etc - not difficult but a bit time
consuming...any thoughts?
 
P

Pecoflyer

DJ;284178 said:
Thks EvilTony,
Problem is that one column has yes, no - the other has UK, US etc etc
so
need to know how many in UK said yes, no - how many in US said yes, n
- do
you see the problem?
Click to expand...

Hi,
a possible solution would be using a Pivot Tabl

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
D

Don Guillett

Hard to tell without seeing your data and layout. If desired,send your
workbook to my address below with a snippet of this msg and I'll take a
look.
 
D

DJ

Don,
thanks for reply - here is baisc layout:

Column A Column B
Yes UK
No UK
No US
No UK
Yes US

and so on...

Just need to know how many people said YES in UK, NO in UK and same for
US...sheet is larger but this is basically what I have.
 
E

EvilTony

Put a name on the columns - you'll need these.

Call Column A "Response", for example, and call Column B "Country".

Then make a pivot table.
Select all the cells including the rows with their names.

Drag "Response" to the ROW data area;
Drag "Country" to the Column data area:
Drag "Country" (again) to the DATA area and select "Count of Country" as the
"summarise by" option.
 
J

Jarek Kujawa

now I can see that too
thks Don
;-)

Glad to help. The earlier sumproduct solution was simply missing a  (
--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Pokaż cytowany tekst -
Click to expand...
 
Top