Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Location
    Columbia, SC
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Billy,

    Welcome to the Lounge as a New Poster!

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

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Aug 2015
    Location
    Columbia, SC
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Billy,

    So what values are displayed in the cells on your worksheet? Yes/No or the Numbers?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Aug 2015
    Location
    Columbia, SC
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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
    Attached Files Attached Files

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

    BillyE (2015-08-27)

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Very Inventive...Nice work!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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