# Formulas count cells that do NOT equal a cell

1. ## 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. ## 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. ## 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. ## 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.

5. ## 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. ## Re: Formulas count cells that do NOT equal a cell

Put this in cell E37 and fill right:

Formula:
`Please Login or Register  to view this content.`

Hope this helps!

Vince

7. ## 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

8. ## 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!

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

#### 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