Quick. Help me create an excel sheet

Factual Questions
1

How do I do this in Excel?

I need:



Column A     | Column B   | Column C    | Column D   | Column E   | Column F
Q. 1         | Yes/No     | Q. 1         | Yes/No     |  Q. 1         | Yes/No
Q. 2         | Yes/No     | Q. 2         | Yes/No     |  Q. 2         | Yes/No
Q. 3         | Yes/No     | Q. 3         | Yes/No     |  Q. 3         | Yes/No

Yes/No should be a drop down list.

Now, it should add up all the “Yes” answers in a given column, e.g. Column A = 2 Yes answers. Then it should multiply the result of each of those columns, e.g. If Column A = 2 Yes, Column C = 1 Yes, and Column C = 3 Yes, it should multiply 2x1x3 = 6 and give me this result on a new row.

e.g.



Column A     | Column B | Column C    | Column D   | Column E   | Column F   |
Q. 1         | Yes     | Q. 1         | Yes     |  Q. 1         | Yes        |
Q. 2         | Yes     | Q. 2         | No      |  Q. 2         | Yes        |
Q. 3         | No      | Q. 3         | No      |  Q. 3         | Yes        |

Result = 6

Does anyone know how to do this in Excel?

If you can send me a template with this logic inbuilt that would be splendid. My email is in my profile.

Thanks a lot.

2

I would hide extra columns in there with conditional statements saying Yes=1, No=0, the have sums at the bottom of those columns that are then fed into a formula showing your “Result”.

=IF(B2=“Yes”,1,0), etc.

I don’t know about dropdown menus, though.

3

Thanks, gigi. Thing is, I don’t know how to do this in excel. I figured out how to create a drop down list, but I haven’t figured out the formulas, and I’m short on time.

I was hoping someone could spell it out for me.

4

Ok, thanks gigi! That worked for me. I’ve done a crude job of it, but atleast it works :slight_smile:

5

There are probably better ways of setting up the spreadsheet as a whole.

But, following on gigi’s idea:

Put this formula into cell b5:
=IF(B2=“Yes”,1,0)+IF(B3=“Yes”,1,0)+IF(B4=“Yes”,1,0)

Copy cell b5 to d5 and f5

Then put this formula into another cell:
=b5d5f5

That cell gives you the product you want

6

Hi Giles, yes that seems to be a better way to do it.

Great, it looks just how I want it now. Thanks guys!

7

The COUNTIF function is designed for this.

=COUNTIF(Range, “Yes”) will tell you how many times the word Yes appears in a range.