HELP needed counting cells
 

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

HELP needed counting cells
 

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")
--
It's a little fit bunny, this feeling inside


"DJ" wrote:

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

HELP needed counting cells
 

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


"EvilTony" wrote:

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")
--
It's a little fit bunny, this feeling inside


"DJ" wrote:

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

HELP needed counting cells
 

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

"DJ" wrote:

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


"EvilTony" wrote:

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")
--
It's a little fit bunny, this feeling inside


"DJ" wrote:

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

HELP needed counting cells
 

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

On 26 Mar, 11:12, DJ wrote:
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?
--
DJ



"EvilTony" wrote:
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")
--
It's a little fit bunny, this feeling inside

"DJ" wrote:

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!!
--
DJ- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

HELP needed counting cells
 

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


"CVinje" wrote:

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

"DJ" wrote:

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


"EvilTony" wrote:

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")
--
It's a little fit bunny, this feeling inside


"DJ" wrote:

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

HELP needed counting cells
 

DJ;284178 Wrote:
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?
--
DJ


"EvilTony" wrote:

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")
--
It's a little fit bunny, this feeling inside


"DJ" wrote:

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

Hi,
a possible solution would be using a Pivot Table


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=79367

HELP needed counting cells
 

Not working I'm afraid - any other thoughts?
--
DJ


"Jarek Kujawa" wrote:

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

On 26 Mar, 11:12, DJ wrote:
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?
--
DJ



"EvilTony" wrote:
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")
--
It's a little fit bunny, this feeling inside

"DJ" wrote:

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!!
--
DJ- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

HELP needed counting cells
 

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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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!!
--
DJ

HELP needed counting cells
 

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


"Don Guillett" wrote:


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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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!!
--
DJ

HELP needed counting cells
 

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.

--
It's a little fit bunny, this feeling inside


"DJ" wrote:

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


"Don Guillett" wrote:


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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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!!
--
DJ

HELP needed counting cells
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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.
--
DJ


"Don Guillett" wrote:


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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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!!
--
DJ

HELP needed counting cells
 

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


"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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.
--
DJ


"Don Guillett" wrote:


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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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!!
--
DJ

HELP needed counting cells
 

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

"DJ" wrote in message
...
Don - you are a star. Big thanks for this.
--
DJ


"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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.
--
DJ


"Don Guillett" wrote:


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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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!!
--
DJ

HELP needed counting cells
 

now I can see that too
thks Don
;-)

On 26 Mar, 19:29, "Don Guillett" wrote:
Glad to help. The earlier sumproduct solution was simply missing a Â* (
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"DJ" wrote in message

...



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

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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.
--
DJ

"Don Guillett" wrote:

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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DJ" wrote in message
...
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!!
--
DJ- Ukryj cytowany tekst -

- Pokaż cytowany tekst -