+ Reply to Thread
Results 1 to 15 of 15

Google Sheets: Sum of 2 Options?

  1. #1
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Google Sheets: Sum of 2 Options?

    Hi, everyone! I'm a newbie at Google Sheets and wondered if I could get some help on a...(formula? Data validation?) for a chart I'm working on.

    What I'm looking to do is to make 2 drop down menus, then reflect the sum of those 2 values in a 3rd cell.

    For example;

    Box 1: January (which is the first month so it's value is 1) (But it would only show January in the list, not '1')
    Box 2: March (which is the third month so it's value is 3) (But it would only show March in the list, not '3')
    Sum: 4

    So to the person using the sheet it looks like;

    [January] [March] [4]

    But naturally if they select different options, the sum would change.

    [April] [December] [16]

    I have the 2 dropdown menus, but I have no idea how to attach each option to a value...and even then, I have no clue how to reflect the sum of those values in the 3rd cell. Any help with this would be appreciated!

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Google Sheets: Sum of 2 Options?

    Assuming the list of months in range I1:I12 and cells G1 and H1 having data validation lists from the mentioned range, the formula is;

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Re: Google Sheets: Sum of 2 Options?

    Hi, thank you! After tinkering around, I'm extremely close to a solution! So far what I have is...

    =ArrayFormula(VLOOKUP($C$26,$B$2:$O$22,{14},FALSE))

    And this returns the correct values for the first drop down list options; do you know what I would need to type so that it considered the sum of the 2nd box? (Which is in cell D:26) and then add the 2 numbers together? Would this be where the =match would be used?

    I've also tried; =ArrayFormula(VLOOKUP($C$26,$B$2:$O$22,{14},FALSE))&ArrayFormula(VLOOKUP($D$26,$B$2:$O$22,{14},FALSE))&SUM(C26:D26) and this generates the 2 values in the same cell and puts a 0 at the end of them...but I need it to add them together lol!
    Last edited by LynnLeighton; 09-13-2021 at 05:55 PM.

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: Sum of 2 Options?

    you do not need to assign values, and you do not need arrayformulas or vlookup.

    the solution provided by Haluk does exactly what you indicated in post #1.Screen Shot 2021-09-14 at 12.14.14 pm.png

  5. #5
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Re: Google Sheets: Sum of 2 Options?

    Thank you! I have it working to a point, but now the value it's returning is incorrect? (I tried linking to the sheet but I have to post more, so I'll use a screenshot for now.) The 2 roles, combined, should total to 17; but it returns 89? The formula I used was; =match(C26,B2:B22,0) + match(D26,B2:B22,0)

    The roles are worth the values found in column O.
    Attached Images Attached Images
    Last edited by LynnLeighton; 09-14-2021 at 02:12 AM.

  6. #6
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: Sum of 2 Options?

    solution retracted by janmorris


    the image above is not the same as the original enquiry
    Last edited by janmorris; 09-14-2021 at 02:15 AM.

  7. #7
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Re: Google Sheets: Sum of 2 Options?

    Ah, I see, edited!

  8. #8
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Re: Google Sheets: Sum of 2 Options?

    Quote Originally Posted by janmorris View Post
    solution retracted by janmorris


    the image above is not the same as the original enquiry
    I used months as a hypothetical example, since my actual sheet is a bit weird to explain--but the functions are the same. The 2 fields populated by 1 list, then their values added to a 3rd field's result? I just didn't know how to explain it; but rather than 1 list of months, it's 1 list of 'roles' and each one has a kind of difficulty value. Depending on the 2 roles being referenced, their difficulty is meant to be added up and returned. Sorry if I'm confusing things, I thought the functions needed for both examples would be the same.
    Last edited by LynnLeighton; 09-14-2021 at 02:25 AM.

  9. #9
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: Sum of 2 Options?

    based on the revised request, try this formula in cell E26:
    Please Login or Register  to view this content.
    the functions are not the same due to a list of months not being quite the same as looking up non-sequential values attributed to different items.
    Last edited by janmorris; 09-14-2021 at 02:44 AM.

  10. #10
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Re: Google Sheets: Sum of 2 Options?

    Thank you! I tried it out but the addition is still coming out wrong--I really can't figure out where Sheets is even pulling the data from to get it's numbers. I put in 2 fields that should total to 15 and it returns 23...then I put in different fields that would total 21 and it shows 13? It's durping me for real lol!

    But it could be that this idea isn't something Sheets is cut out for. I'd love to be able to just assign a variable to a word or cell and have that be added together, but I know Sheets doesn't have that kind of function. It's alright if this isn't solvable, though! I really appreciate all the help!

  11. #11
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: Sum of 2 Options?

    are you able to attach a txt file that has a share link (with editor access) to your file (a sample file or duplicate may be preferred)?

  12. #12
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Re: Google Sheets: Sum of 2 Options?

    Sure, here you are! Nice idea!
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: Sum of 2 Options?

    i discovered that the sheet was still using the match style formula as suggested for sequential months, rather than the vlookup style formula needed for non-sequential numbers attributed to the names.

    this has been corrected, and the "Stars needed" now gives correct total.

  14. #14
    Registered User
    Join Date
    09-13-2021
    Location
    Chicago, Illinois
    MS-Off Ver
    18
    Posts
    8

    Re: Google Sheets: Sum of 2 Options?

    KYAAAAAAAAAH THAT IS SO COOL! Seriously, thank you 10000000000 times for helping me to get this working! o(≧▽≦)o You really didn't have to do all this, but it is up and running now! Amazing!! Thank you so much!

  15. #15
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Google Sheets: Sum of 2 Options?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Google Sheets: Condition formatting in Google sheet
    By cyee in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 02-18-2021, 05:35 PM
  2. [SOLVED] Google Sheets: Update slave/child sheets from Master/Parent sheet
    By JGREEN17 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-29-2021, 03:03 PM
  3. [SOLVED] Google sheets script to download file and save it to google drive folder and use it's data
    By western in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 01-18-2021, 01:31 PM
  4. [SOLVED] Google sheets vba to download file and save it to google drive folder and use it's data
    By western in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-14-2021, 08:48 AM
  5. 2 Column Search Options then Show Results (Google Sheets)
    By thebrucekt in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 11
    Last Post: 04-18-2020, 10:31 AM
  6. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  7. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1