Formula needed to give a % of yes's in one column depending on text in another

Hello,

I have spent a few hours trying different formulas and arrays to try and fix a spreadsheet.

I have a list of names in one column and a list of yes/no in another column. I need to show a resulting % of yes's a certain name has given. For example:

Dave Yes
Stuart No
Dave Yes
Dave Yes
Stuart No
Dave No
Stuart Yes
Dave No
Dave No

So Dave answered yes 3 out of 6 times, so this should show 50% in a separate results cell.

Other information: Each column is infinite as it will be added to throughout the course of a year, and the data entry is on a separate workbook.

Any help would be appreciated, thanks in advance

The solved file is attached.

Hi all, thanks for the speed responses, it's my first time on this forum and I'm impressed!!

nathansav is correct, I'm using 2003 (upgrade is imminent apparently) so I can't use COUNTIFS.

Hello again.

Thanks Richard Buttery, I think I understand it, and I hadn't tried using the SUMPRODUCT before, but when I implemented it into my workbook I get an error.

I have just edited the spreadsheet I'm working on (I've had to change headers etc) and I've attached it. As you can see, there are 2 workbooks, the 'Single page' is for data input and the 'Results' book is for...erm...Results ha ha!! You will notice I have worked out some of the questions, but I'm struggling on the % of yes's.

Thank you for that, it works, but it just looks like voodoo magic at the moment! I'm trying to understand it as I have to perform a similar formula in the cells on row 14 (on the 'Results' book) and I changed the cell string from D$4 to C$13 but I get a #DIV/0! error.

Are the D4, E4 and F4 cells on the 'Results' book the only cells with formulas? I dont understand how it references the information in the columns on the 'Single page' book!

Excellent, that's the first time I've heard of dynamic range, so that's opened up a new world for me to play with. I've managed to have a little read up on it and I've created new ranges for the other info I need. Thanks for your help.

I would definately prefer to know how it's done, but after a stubborn couple of hours trying to work it out via Google, I thought I should ask!

I think I'm due an upgrade! Thanks again for your help.

I think that's what's confusing me is that it's multiplied but it shouldn't be as it's finding a % of something within something else.

I've re-attached the workbook, with your amended formula. It's giving me a result of 33% but it should be 100%.

This is what I'm trying to do:
What is the % of Dave(name) saying Yes(info4) when he's Purple(info1)!

So at the moment there is only one entry that matches, and it is currently 'yes', so it should be 100%. If I added another entry of 'Purple - Dave - No', then the result should change to 50%.

Anyone got a clue on this?

As above, I need to find % of Dave(name) saying Yes(info4) when he's Purple(info1)

Mr Buttrey has kindly given me the following formula to give me the % of Dave saying Yes, which is placed in the results section:
=SUMPRODUCT((names=C$9)*(YN_4="Yes"))/COUNTIF(names,C$9)

Which coincides with the Dynamic Name range:
'names' =OFFSET('Single page'!$F$2,0,0,COUNTA('Single page'!$F:$F),1)
'YN_4' =OFFSET('Single page'!$J$2,0,0,COUNTA('Single page'!$J:$J),1)

It's the first time I've used Dynamic Name ranges, so I'm trying to get to grips with it, but now I want to add the 'when he's purple' part to it and I'm struggling!

Any help would be greatly appreciated.

first tell me was my method wrong? wat diffiiculty did u face using my method??