+ Reply to Thread
Results 1 to 10 of 10

What formula can I use to get a value from a range?

  1. 05-08-2020, 11:44 PM #1
    eli-vergara
    eli-vergara is offline
    Registered User
    Join Date
    05-02-2016
    Location
    85132
    MS-Off Ver
    2013
    Posts
    12

    Question What formula can I use to get a value from a range?

    So, I have a table with rows filled with scores, N/A, 0 (zeroes), and blanks, on different rows (depending on the row value in column A). Only one value appears in each column.

    Since these values are scattered in different rows, I have one row above this table that sums each entire table column, to bring these values into one row.
    I then plan on using these values on a chart.
    The problem is, Excel takes the N/A's and outputs a 0 as the result of the =SUM(B2:B30) for example. and it also returns 0 as the sum of the blank rows.

    I only want it to bring up the value on that cell, not sum it, but I don't know what formula would do that.

    I have tried to use an IF statement, (if the sum <> 0 show the value, else show " "), and that works to an extent, but it doesn't deal with N/A's, blanks and zeroes. I need zero scores to count, but N/A or blanks to show that value also, so I already know =SUM is not the right formula.

    What can I use instead?

    Thanks!
    Reply With QuoteRegister To Reply
  2. 05-08-2020, 11:49 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: What formula can I use to get a value from a range?

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 05-09-2020, 12:04 AM #3
    eli-vergara
    eli-vergara is offline
    Registered User
    Join Date
    05-02-2016
    Location
    85132
    MS-Off Ver
    2013
    Posts
    12

    Re: What formula can I use to get a value from a range?

    Thank you!!
    I have atached the dummy file.
    Would you mind taking a look at it?

    Thanks for responding!!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 05-09-2020, 12:06 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: What formula can I use to get a value from a range?

    Sure, give me a few mins
    Reply With QuoteRegister To Reply
  5. 05-09-2020, 12:08 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: What formula can I use to get a value from a range?

    Try this, copied sacross...
    =IF(COUNT(B3:B12),SUM(B3:B12),"")
    Reply With QuoteRegister To Reply
  6. 05-09-2020, 12:13 AM #6
    eli-vergara
    eli-vergara is offline
    Registered User
    Join Date
    05-02-2016
    Location
    85132
    MS-Off Ver
    2013
    Posts
    12

    Re: What formula can I use to get a value from a range?

    It didn't work. It does not return the value from that cell, It counted the score, for example, if I had 50 in column B, it resulted in 1.
    Also, it returned blank when the value was N/A.
    Basicaly all I am looking for is some formula that takes whatever that value is in that range and shows it in the results row. If it is N/A, show N/A, if it is 50, show 50, and if it is blank, show blank.

    Thanks!!
    Reply With QuoteRegister To Reply
  7. 05-09-2020, 02:18 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,736

    Re: What formula can I use to get a value from a range?

    In B14 copied across:

    =IFNA(LOOKUP(2,1/(B$3:B$12<>""),B$3:B$12),"")

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    2
    		 Score 1 Score 2 Score 3 Score 4 Score 5 Score 6
    3
    		 Test 1
    100
    4
    		 Test 2
    80
    5
    		 Test 3
    0
    6
    		 Test 4
    7
    		 Test 5
    8
    		 Test 6 N/A
    9
    		 Test 7
    10
    		 Test 8
    60
    11
    		 Test 9
    12
    		 Test 10
    13
    14
    		 Results
    100
    80
    		 N/A
    0
    60
    Sheet: Sheet1
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  8. 05-09-2020, 11:07 AM #8
    eli-vergara
    eli-vergara is offline
    Registered User
    Join Date
    05-02-2016
    Location
    85132
    MS-Off Ver
    2013
    Posts
    12

    Re: What formula can I use to get a value from a range?

    Thanks!!! this worked great!!!
    Last edited by eli-vergara; 05-09-2020 at 11:09 AM.
    Reply With QuoteRegister To Reply
  9. 05-09-2020, 11:15 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,736

    Re: What formula can I use to get a value from a range?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  10. 05-09-2020, 01:30 PM #10
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: What formula can I use to get a value from a range?

    Crosspost

    https://www.mrexcel.com/board/thread...blank.1133522/
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to identify and show address of range cells related to range of dates
    By Mr.Castle in forum Excel General
    Replies: 3
    Last Post: 09-09-2019, 03:59 PM
  2. [SOLVED] SUMIF formula with dynamic column range and then expand the width of SUM range
    By gravythief in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2017, 08:36 AM
  3. [SOLVED] Make data range in formula shift for a whole range when copying cell
    By Zaron in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-21-2016, 06:37 AM
  4. Function/Formula to extra rows from one range based on a date period to another range?
    By Norcal1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-24-2015, 03:00 AM
  5. Constant a range in find formula in a cell even after insert a row between desired range
    By vinothmcc in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-02-2015, 11:18 AM
  6. Formula to look at range of text and IF statement if values contained in differing range
    By EGFish54 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-07-2014, 01:42 PM
  7. formula to return blank value if cell va;ie not in range but vlookup if in range of cells
    By david1987 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2013, 07:23 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1