HELP needed counting cells

[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!!

 

[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")

 

[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?

 

[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

 

[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"

then copy the list to D1:Z1...

pls click YES if this post helped

 

[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?

 

[DJ]

Not working I'm afraid - any other thoughts?

 

[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.

 

[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.

 

[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.

 

[Don Guillett]

Sumproduct will do it. Adapt to suit
=sumproduct((a2:a22="yes")*(b2:b22="uk"))

 

[DJ]

Don - you are a star. Big thanks for this.

 

[Don Guillett]

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

Don - you are a star. Big thanks for this.

 

[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 -