+ Reply to Thread
Results 1 to 4 of 4

Sum only numeric values in range (and disregard cells with inactive formulas)

  1. #1
    Registered User
    Join Date
    10-17-2017
    Location
    Philadelphia
    MS-Off Ver
    Professional Plus 2016
    Posts
    5

    Sum only numeric values in range (and disregard cells with inactive formulas)

    I'm looking for a formula that will sum the numeric values of three cells (K16, K17, K18). The tricky part is that each of those three cells is using a formula to populate its number. Simply adding them up or using the sum function doesn't work if any of those three cells are not calculated. I want to get the total sum (in cell K20) using whichever cells are populated with a number (even if it's only using 1 or two cells). K20 should also be blank or "N/A" by default (and not 0). I've uploaded a sample workbook. Thank you for the help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: Sum only numeric values in range (and disregard cells with inactive formulas)

    change your formula in

    K17=IF(K12>0.67, "3", IF(K12>=0.5, "2", IF(K12>=0.25, "1", "0")))+0

    K18=IF(K13>=0.5,"1","0")+0

    After
    K20=SUM(K16:K18)


    "3", "1","0" is a text not a number
    Last edited by CARACALLA; 07-17-2019 at 05:39 PM.

  3. #3
    Registered User
    Join Date
    10-17-2017
    Location
    Philadelphia
    MS-Off Ver
    Professional Plus 2016
    Posts
    5

    Re: Sum only numeric values in range (and disregard cells with inactive formulas)

    Thanks for your response, CARACALLA - it's certainly better now. However, I'm finding that the total store store isn't calculated when Healthy Aisles and/or Checkout points are missing. Any thoughts on how to account for that? (This is basically a store audit form and I want to make sure I can still get the score even if someone forget to fill out a section.)

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,554

    Re: Sum only numeric values in range (and disregard cells with inactive formulas)

    It may help us understand if you upload a sample spreadsheet that demonstrates the issue along with (manually included) the results you expect under these conditions.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Central tendency formulas that disregard specific values before giving a result
    By TnD_Guy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-15-2015, 01:45 PM
  2. Copy only cells that contain values from a range cells that contain formulas to next row
    By gjwilson1216 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2015, 11:12 AM
  3. How to remove only cells with numeric values from range
    By ExcellentM in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2014, 01:37 PM
  4. [SOLVED] Sum of numeric values within cells also containing non-numeric characters
    By Mike Brewer in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 04-24-2013, 09:16 AM
  5. [SOLVED] Need to average numeric values from a range of formulas.
    By ndrobinson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2013, 06:10 PM
  6. Replies: 1
    Last Post: 11-03-2011, 11:43 AM
  7. Using Numeric Values from Formulas
    By Natalie Tarry in forum Excel General
    Replies: 4
    Last Post: 08-22-2005, 11:19 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1