1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Microsoft Excel Help Needed

Discussion in 'Business Applications' started by Timewell, Feb 8, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    Hi guys!

    I have encountered a problem with Microsoft Excel. The following formula does not seem to work for some reason and I nor another good friend can see why. Can you guys help me?

    The formula is:

    Code:
    =COUNTIF(B59,B56,B53,B50,B47,B44,B41,B38,B35,B32,B29,B26,B23,B20,B17,B14,B11,B8,B5, ">=0")
    It gives me the error message:

    I have noticed however that when I was clicking on the cells to give them the range, that the first three had coloured boxes around them and the rest hadn't for some reason. It keeps indicating that cell B53 has an error in it but it is a blank cell.

    Many thanks for your help guys!
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    1. >0 will count cells with formulas.

    2. What's in the cells in between? I.e., B6, B7, B9, B10...

    I suspect you don't have your data laid out very well. Wanna attach your file so I can have a look?
     
  3. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    It won't let me attach it....
     
  4. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Sorry, Time. I still don't get it.

    Make a copy of your file, change the extension from xls to txt and load it up.

    And if you can, put some textboxes/arrows (use the drawing toolbar) to show where you want your formulas.
     
  6. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    What don't you get? You know that the spreadsheet is interactive so you could play around with the formula. It dislikes the 3rd cell in the formula I gave you and anything after it dislikes...:(
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    What don't I get?

    I have no idea in which cells you need your formula or which cells you're counting if...

    ...and I have no idea what your worksheet does.
     
  8. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    I've been told that the count if function will not work because it needs a range and not unique cells. So basically I need another function that will allow me to count individual cells while still maintaining the criteria of ">0"
     
  9. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Figuring out the formula is not the issue....

    It's the layout of your file.

    Why do you have 3 rows for each at the top? Why not just one row?
     
  10. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Try this:

    =COUNTIF(B56:B59:B50:B53:B44:B47:B38:B41:B32:B35:B26:B29:B20:B23:B14:B17:B8:B11:B5, ">=0")
     
  11. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    For that particular bit the formula now works. BUT!

    For the cell which calculates the number of wins or losses, it gets interference from a cell which contains "Bonus Points".

    For example, I using the formula you gave me and changing the cell references to those suiting "wins". Would mean that it calculates cells that are not needed.

    Example, it calculates whether a match has been won or lost in the set of cells B5 - D5, B7 - D7 (Horizontally) and B5 - B7 and D5 - D7 (Vertically). That leaves you win a box. The criteria for a win is ">0" and thus if the score was 2 - 1, Excel would calculate it as a win.

    So, I enter the score as 2 - 1. Thus Excel should say, Played 1, Won 1, Drawn 0 and Lost 0 with "For" = 2 and "Against" = 1. I have also typed in the number of bonus points to equal 3 (D5). It now gives it as For = 5 and played 2 games. I therefore need a formula to count individual cells right?
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Sorry, time. I just can't do anymore without a real, live spreadsheet to work with.

    I can't see row and column headings or anything in that online thing.
     
  13. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    Well do you have an e-mail I can mail it to or something?
     
  14. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    Is there another way of sending it to you or don't you want me to mail it? It won't upload for some reason and CSV files don't show the formula.

    So can I mail it you?
     
  15. Timewell

    Timewell Thread Starter

    Joined:
    Feb 8, 2003
    Messages:
    58
    I take it that is a no? Why don't you PM me your e-mail?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/117700

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice