+ Reply to Thread
Results 1 to 8 of 8

Formulas count cells that do NOT equal a cell

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Formulas count cells that do NOT equal a cell

    I need help finding a formula to add cells that do not equal a cell.

    If the person LOCKED the team then they lose a point I've been trying for DAYS to figure this out but I dont want people to start negative points and thats the only way i can figure out how to do it.

    help would be great!!!!!Copy of Copy of nfl (Repaired).xlsx

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Formulas count cells that do NOT equal a cell

    boll55,

    I'm sorry to say that I can't answer the question you are asking - I'll look into it when I get the chance - hopefully by then someone else will come to your rescue.

    However, I will offer one suggestion.... on sheet 1 (2), in cells E37:AB37, you have an incredibly long formula trying to do something that a simple short formula could handle much more gracefully.... your formula in E37 is this:

    Please Login or Register  to view this content.
    When you could simply use this:

    Please Login or Register  to view this content.
    The " -COUNTBLANK(E11:E29) " portion removes the count of blank cells from the sum of correct matches. You can paste the formula above in cells E37 thru AB37.

    Cheers,
    Vince

  3. #3
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Formulas count cells that do NOT equal a cell

    Thanks! I try my best at making things work and I usually can figure them out the hard way but it is nice when someone shows me an easier way to do something!

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Formulas count cells that do NOT equal a cell

    I can't understand the specifics of your need, but the formula =COUNTIF(A1:A10,"<>"&B1) will return how many entries in A1:A10 are different than B1.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Formulas count cells that do NOT equal a cell

    Ok the rules of the game are if you get the team correct that you picked you get one point

    if you LOCK a team (you dont have to lock a team if you dont want to) you get 2 points if they win (1 point for picking the correct team and 1 point for selecting them as a lock)


    if you select a lock and they dont win you get zero points for picking the wrong team and lose a point for locking them

    i need formulas to do all of this for me so all i have to do is enter who wins and the sheet will take care of everything else

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Formulas count cells that do NOT equal a cell

    Put this in cell E37 and fill right:

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


    Hope this helps!

    Vince

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Formulas count cells that do NOT equal a cell

    boll,

    I suddenly realized that you were using row 37 as a 'helper' row along with a few other rows with the 'lock' formulas. I modified your originally posted sheet to streamline things and get the total points posted in row 3, as I assume that is where you want them to be. Please see the spreadsheet (Modified by Moo) attached and let me know if you have any questions... plus - it solves the "starting with a negative point value" thing.

    - Vince
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-06-2012
    Location
    Quincy,IL
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Formulas count cells that do NOT equal a cell

    Thank you so much! The boys at the office will be so happy with the football picks this week!

+ 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