+ Reply to Thread
Results 1 to 19 of 19

skipping #N/A

  1. #1
    Registered User
    Join Date
    04-02-2019
    Location
    LT
    MS-Off Ver
    2007
    Posts
    23

    skipping #N/A

    Hello,

    Is it possible to skip cells that contains #N/A in table and count only sum of valid numbers ?
    Last edited by saligatvis; 05-06-2019 at 06:10 AM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: skipping #N/A

    With a macro, but I would make sure to avoid #N/A at all times and if you correct your formulas, that would be no problem using the ISNA function or IFERROR, in all those cases the result can be 0 and will not influence the adding and or counting.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    04-02-2019
    Location
    LT
    MS-Off Ver
    2007
    Posts
    23

    Re: skipping #N/A

    So currently i am ussing formula =SUBTOTAL(9;BT2:BT220) how could i add iferror ?

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: skipping #N/A

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    However, I think, you have to deal with error (#N/A) in particular cells in BT2:BT220. If you have formulas there, use IFERROR like above.
    If you get data from other source as value, and #N/A can appear, imho instead of SUBTOTAL, you can use:

    Please Login or Register  to view this content.
    accept with Ctrl+Shift+Enter.
    Last edited by KOKOSEK; 05-06-2019 at 06:51 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  5. #5
    Registered User
    Join Date
    04-02-2019
    Location
    LT
    MS-Off Ver
    2007
    Posts
    23

    Re: skipping #N/A

    No still doesn't work with both of them he misses eror cells and all others

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: skipping #N/A

    You must check that none of the cells on the range shows #N/A
    That’s troubleshooting, patience and perseverance

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: skipping #N/A

    Without any file and sample of hwt YOU have and are doing, help ends here, it's a question of making sure that in none of the cells the error #N/A is not there, else no go

  8. #8
    Registered User
    Join Date
    04-02-2019
    Location
    LT
    MS-Off Ver
    2007
    Posts
    23

    Re: skipping #N/A

    I tried couple of versions added file to attachment
    Attached Files Attached Files

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: skipping #N/A

    If you have XL2010 or above =AGGREGATE(9,6,H13:H25)

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: skipping #N/A

    What you need to do is make sure you do NOT van invalid values in the table, if the formula fills this returns #N/A make the value 0 and hide 0 values in the cell formatting

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: skipping #N/A

    The aggregate function of course is better but... the OP has 2007

  12. #12
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: skipping #N/A

    @saligatvis - You have put my formula but you did not accept it with CSE.

    Put this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and accept with CSE then will be fine.

    That's from you attached file:

    Capture.JPG

  13. #13
    Registered User
    Join Date
    04-02-2019
    Location
    LT
    MS-Off Ver
    2007
    Posts
    23

    Re: skipping #N/A

    Wha does it mean accept with CSE?

    Attachment 623254

  14. #14
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: skipping #N/A

    Quote Originally Posted by Keebellah View Post
    The aggregate function of course is better but... the OP has 2007
    Insofar the profile is still up to date...

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: skipping #N/A

    CSE means that it's an array formula.

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    However, this will also work and works with just Enter:

    =SUMIF(H13:H25,">-1000000")

    Change the -1000000 if you need to.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  16. #16
    Registered User
    Join Date
    04-02-2019
    Location
    LT
    MS-Off Ver
    2007
    Posts
    23

    Re: skipping #N/A

    When i accept with CSE it works but it counts even hidden rows which i am trying to avoid

  17. #17
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: skipping #N/A

    I am lost now. Do you want to count or sum.

    In your post:
    "So currently i am ussing formula =SUBTOTAL(9;BT2:BT220) how could i add iferror ?"
    I get that you want to SUM range, right? But if you got error in range you have got error in this sum.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    accepted with Ctrl+Shift+Enter, gives you SUM in range even if you have got errors in particular cells (0 value is assign for them).

    What do you mean as hidden rows?

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: skipping #N/A

    Post a sample sheet that shows EXACTLY what you want. You have suddenly mentioned hidden rows. There were none in your attachment. We cannot guess what is in your head. Show us on a sample sheet.

    Also, are these actual #N/A errors... or are they just typed in? In your sample they are just typed in.
    Last edited by Glenn Kennedy; 05-08-2019 at 06:24 AM.

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: skipping #N/A

    I was just going to say that avoid others having to think what you want. explain it clear and concisely, YOU understand it but don't expect other to do so with an incomplete explanation and no knowledge of the actual data

+ 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] Skipping ID
    By Billy Spivy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-17-2018, 06:39 AM
  2. [SOLVED] Skipping row if condition not met
    By Andrew87.. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2016, 05:02 PM
  3. Skipping Row v2
    By HeHeHaHa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-18-2014, 01:29 AM
  4. Replies: 1
    Last Post: 05-21-2014, 04:30 PM
  5. For Each Statement Skipping Every Second Row
    By Kapanther in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-12-2013, 09:40 PM
  6. Skipping Loop
    By yasar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2013, 06:15 AM
  7. Replies: 0
    Last Post: 04-20-2005, 12:06 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