# Thread: Assign different values to Yes/No (Yes can be 1,2, or 3, No is 0) and total

1. ## Assign different values to Yes/No (Yes can be 1,2, or 3, No is 0) and total

Trying to assign a value to Yes/No drop down list, then total them. The cells are from B7 to G7 and the total should go in I7. The basic formula for I7 worked for the first - =SUMIF(B7="Yes",3,0) but then B8 could be =SUMIF(B7="Yes",2,0) what command do I use to add the different values of "Yes" to the total in I7.

Been a long time since I coded any. Thanks for any help.

-Billy

2. Billy,

Welcome to the Lounge as a New Poster!

The formula I think you want is: =COUNTIFS(B7:G7,"Yes")
BillyECountIfs.JPG

HTH

3. Thanks for the help. That would work great if all my "Yes" answers had the same weight, but B7 may be worth 3 points on a scale, while the C7 could be 0, D7 "Yes" could equal 2, etc...

But I'm getting closer, here...

4. Billy,

So what values are displayed in the cells on your worksheet? Yes/No or the Numbers?

5. Yes/No drop down with Data Validation that is stored on the next blank sheet as A1:A2 (Yes and No, named "YesNo) and called from the data validation by name. The goal is to be able to click a drop-down, select "Yes" and that is an important question they answer, so it is worth a value of 3, the next "Yes" they click may not be as important, so it may only be worth 2 points. Then total them. I think I'll have to put values off to the side, assigned based on what is selected, hide that part of the spreadsheet, then just do a SUM in the totals...

6. Billy,

Here is a User Defined Function that will sum the weighted "Yes(s)" while filtering out the "No(s)". In cell I7, enter the following formula:

=SumWt(B7:G7)

In a standard module, enter the following code:
Code:
```Public Function SumWt(rng As Range) As Integer
Dim s, cell As Range, count As Integer
s = Array(3, 2, 1, 3, 1, 2)
count = 0
For Each cell In rng
If UCase(cell) = "YES" Then
SumWt = SumWt + s(count)
End If
count = count + 1
Next cell
End Function```
Adjust the code line s = Array(3, 2, 1, 3, 1, 2) with the weighted values of the yes B7 through G7. Note: Even though you have data validation, the Yes/No is not case sensitive.

HTH,
Maud

SumWt1.png

7. ## The Following User Says Thank You to Maudibe For This Useful Post:

BillyE (2015-08-27)

8. Maud,

Very Inventive...Nice work!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•