+ Reply to Thread
Results 1 to 12 of 12

count value in all cells and display result even with error in some cells

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    31

    count value in all cells and display result even with error in some cells

    hi there.

    i want to count all the values in the blue cells in my timesheet, some of them will have errors in but i still want the result even if not all of them has values in, starting at G and then every 4th cell after that, e.g G,K,O,S,
    if that is possible in any way, what happens is i convert time to decimal, then al the decimal must be counted, so the decimal is in every 4th cell starting at G, but it doesnt want to display the result if not all 31 cell are filled, how can i work around this and get it to display the result like i add time.

    thank you / kind regards

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count value in all cells and display result even with error in some cells

    Can you post a small sample workbook showing what you mean?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: count value in all cells and display result even with error in some cells

    Hi there, for some reason my browser doesnt want to show the manage attachment screen, i do apologize for this, here is a link to it though

    thank you / kind regards

    http://www.4shared.com/dir/amv7gcpC/_online.html

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count value in all cells and display result even with error in some cells

    I am not a member of 4shared... so can't download. Try another free site like skydrive or try to use manage attachments again...

  5. #5
    Registered User
    Join Date
    08-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: count value in all cells and display result even with error in some cells


  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count value in all cells and display result even with error in some cells

    Does this work for you?

    =SUM(IF(ISNUMBER(IF((MOD(COLUMN(G7:DU7)-COLUMN(G7),4)=0)*(G7:DU7),G7:DU7)),G7:DU7))

    confirmed with CTRL+SHIFT+ENTER not just ENTER

  7. #7
    Registered User
    Join Date
    08-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: count value in all cells and display result even with error in some cells

    It must only count the decimal numbers. Will this work? Only every 4th column

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count value in all cells and display result even with error in some cells

    What do you mean by decimal numbers, exactly? Are you saying it should ignore whole numbers? Or just the errors?

    My formula will sum all numbers in every 4th column.. so in your example you should get 17 as a result (8.5 + 8.5)

  9. #9
    Registered User
    Join Date
    08-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: count value in all cells and display result even with error in some cells

    I got it working thank you sir!

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count value in all cells and display result even with error in some cells

    Great. Can you remember to mark your thread as SOLVED too please.

  11. #11
    Registered User
    Join Date
    08-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: count value in all cells and display result even with error in some cells

    hi there sir

    im finaly at home on a decent pc, i got it all working and just want to say thanks.

    Thank you / Kind regards
    +rep

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: count value in all cells and display result even with error in some cells

    You are very welcome. Thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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