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