# Quick. Help me create an excel sheet

**URL:** <https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768>\
**Category:** Factual Questions\
**Created:** [August 26, 2005, 6:14pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768 "2005-08-26T18:14:42Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\
**Post date:** [August 26, 2005, 6:14pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768/1 "2005-08-26T18:14:42Z")

</div>

How do I do this in Excel?

I need:

```auto

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.

```auto

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.

---

<div class="post-metadata">

**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\
**Post date:** [August 26, 2005, 6:30pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768/2 "2005-08-26T18:30:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\
**Post date:** [August 26, 2005, 6:37pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768/3 "2005-08-26T18:37:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\
**Post date:** [August 26, 2005, 6:58pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768/4 "2005-08-26T18:58:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\
**Post date:** [August 26, 2005, 7:03pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768/5 "2005-08-26T19:03:03Z")

</div>

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:  
=b5_d5_f5

That cell gives you the product you want

---

<div class="post-metadata">

**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\
**Post date:** [August 26, 2005, 7:19pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768/6 "2005-08-26T19:19:04Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![Kiminy](https://avatars.discourse-cdn.com/v4/letter/k/6a8cbe/32.png) [@Kiminy](https://boards.straightdope.com/u/Kiminy)\
**Post date:** [August 26, 2005, 9:45pm UTC](https://boards.straightdope.com/t/quick-help-me-create-an-excel-sheet/318768/7 "2005-08-26T21:45:36Z")

</div>

The COUNTIF function is designed for this.

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