Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count / Sum if help.
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 |
#2
|
|||
|
|||
Hi
=SUMPRODUCT(--(B1:B100=1),--(C1:C100="UK")) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Foss" wrote: 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 |
#3
|
|||
|
|||
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 "Foss" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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 "Foss" wrote: 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 |
#6
|
|||
|
|||
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 "Foss" wrote in message ... 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 "Foss" wrote: 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 |
#7
|
|||
|
|||
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. On Tue, 16 Nov 2004 04:35:01 -0800, "Foss" wrote: 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 "Foss" wrote: 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 |
#8
|
|||
|
|||
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 "Foss" wrote: 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 "Foss" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filter and count | Excel Discussion (Misc queries) | |||
Count occurences between dates | Excel Worksheet Functions | |||
Count If Formula | Excel Worksheet Functions | |||
count based on two fields - need quickly | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |