+ Reply to Thread
Results 1 to 7 of 7

Help correcting a round up formula

  1. #1
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Help correcting a round up formula

    Hi,

    I have the following formulae :-

    =sumproduct(roundup(E7:E184,0))

    In column E I have numbers either whole or decemal and wanted to perform a round up fuction so i had the total numbers, however some cells in coulmn E contain words rather than numbers and my formulae above returns #VALUE. is there away to get it to ignore cells that contain words rather the number.

    Example
    E7 :- 2
    E8 :- 2.5
    E9:- box
    E10:- 5
    E11:- 1.75

    The answer im looking for is 12.
    Last edited by Dom.Knight; 03-12-2020 at 09:19 AM.

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Help correcting a round up formula

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Re: Help correcting a round up formula

    Its not liking that either excel is telling me theres an error still

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Help correcting a round up formula

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Re: Help correcting a round up formula

    Yes that works correctly thanks.

  6. #6
    Forum Contributor
    Join Date
    03-14-2017
    Location
    london, england
    MS-Off Ver
    office 365
    Posts
    173

    Re: Help correcting a round up formula

    I have an addional formulae on the same sheet, is there away to adjust this to also round up.

    =SUMIF($B:$B,M7,$E:$E)

    This also adds all the boxes of each specie together which are located on the data section of the sheet.

    Example to what youll see on my attachment.

    N7 = SHOWS 100.5 Id like it to show 101 as there are 101 boxes entered in column E that are cod
    Attached Files Attached Files
    Last edited by Dom.Knight; 03-12-2020 at 07:55 AM.

  7. #7
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Help correcting a round up formula

    Quote Originally Posted by Dom.Knight View Post
    Yes that works correctly thanks.
    if so you have text values in the all cells but not numeric.
    array formula
    =SUM(IFERROR(ROUNDUP(--E7:E184,0),))

    and for 2nd issue
    =SUM(($B$7:$B$100=M7)*IFERROR(ROUNDUP(--$E$7:$E$100,0),))
    Last edited by BMV; 03-12-2020 at 08:25 AM.

+ 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. Excel 2007 : need help correcting a formula
    By berk21 in forum Excel General
    Replies: 2
    Last Post: 03-08-2010, 07:36 PM
  2. Correcting formula
    By mistral in forum Excel General
    Replies: 1
    Last Post: 08-11-2006, 08:50 AM
  3. [SOLVED] need help in correcting the formula
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 07:05 AM
  4. need help in correcting the formula
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  5. need help in correcting the formula
    By azmi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. need help in correcting the formula
    By azmi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] need help in correcting the formula
    By azmi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. [SOLVED] need help in correcting the formula
    By azmi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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