Count / Sum if help.

[Foss]

Good morning all,

I've got a list of results from a questionnaire in a worksheet.
Along the top is the question number, down the side are the people who
answered.

For each question there are five possible answers, they're numbered one to
five.

There's also a Country column which says where the respondent lives.

I want to make a function for each question that can count the occurences of
answer '1' where the country column contains 'UK'.

I can't seem to write a formula to do this for me, it's driving me crazy!
I've struggled with this for nearly three whole days and I'm getting a bit
miffed now!
Any help would be much appreciated.

Cheers,
Foss

 

[Frank Kabel]

Hi
=SUMPRODUCT(--(B1:B100=1),--(C1:C100="UK"))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Paul B]

Foss, here is one way, =SUMPRODUCT((A1:A10=1)*(B1:B10="uk"))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2003

 

[JulieD]

Hi Foss

so in C2 you have Question1
in A3 you have the first person's name
in B3 you have their country
and in C3 you have their choice of numbers 1 through 5
A4 second person etc

so to get the number of people in the UK who answered 1 for Question 1 the
formula would be
=SUMPRODUCT(--($B$3:$B$11="UK"),--(C3:C11=1))

Check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

For details on the SUMPRODUCT function
Cheers
JulieD

 

[Foss]

Thank you all very much for your help, that's just what I was looking for!
Perfect!

Now the only problem is that for some reason excel isn't recognizing the
cell value as a number, just a string.
It's very odd, the number appears in the left hand side of the cell. There's
no extra characters in the cell at all. If I manually type over the cell with
the exact same content then it moves to the right of the cell and is
recognised as a number.
I've written a quick script to add a ' to the beginning of each cell in the
used range then delete it again, hopefully that'll sort it!

Thanks again!

Foss

 

[JulieD]

Hi Foss

easier that than (or if it didn't work), select another cell (maybe on
another worksheet) and copy it, now select your "numbers" and choose edit /
paste special ADD

this should fix them

Cheers
JulieD

 

[Myrna Larson]

The fix is to Edit/Copy a blank cell. Then select the cells containing the
responses and Edit/Paste Special, and select the Values and Add options.

 

[Foss]

That worked very nicely too! Thanks very much indeed.

I have come up against a problem with the sumproduct function though, I want
the range to be based on the header in the first row. So when people enter
the number one, it looks up question one and uses that range. I've got it
working fine for my simpler things like the countif etc. But I can't make it
work with the sumproduct.

Here's the formula I use in place of typing in the range:
OFFSET(data_height,0,SUM(os,VLOOKUP($B$11,col_lk,2,0)))

The 'data_height' named range is just the top to the bottom of the data in
column A.
The 'os' named range is just an offset variable just in case the source data
moves later on.
The 'col_lk' named range is where the details of the fields (ie question
one) and their locations (column 10).

Thanks again,
Foss