count if not 2-8, column by name

Formula will be in sheet ("Notes")

I need a formula to count where value is NOT 2-8 in the column "Zone".
I need to identify the column in sheet ("Data"), I don't know what column, but it will always be named "Zone"

Can anyone help? Thanks! :)
Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
do you mean 2-8  as a text or between 2 to 8 any number from 2 to 8?
Euro5Author Commented:
sorry  - the numbers 2,3,4,5,6,7, or 8. I need to count lines that are NOT those numbers.
Martin LissOlder than dirtCommented:
This macro will find Zone. It could easily be turned into a UDF with any other code you need when you find the zone.

Sub FindZone()
Dim lngCol As Long

For lngCol = 1 To Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    If UCase(Cells(1, lngCol)) = "ZONE" Then
        MsgBox "Zone is column " & lngCol
        Exit For
    End If
Next
End Sub

Open in new window

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Martin LissOlder than dirtCommented:
Here's the UDF.

Usage:
GetZoneCount()

Function GetZoneCount() As Integer
Dim lngCol As Long
Dim lngRow As Long

For lngCol = 1 To Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    If UCase(Cells(1, lngCol)) = "ZONE" Then
        Exit For
    End If
Next
For lngRow = 2 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Cells(lngRow, lngCol).Value < 2 Or Cells(lngRow, lngCol).Value > 8 Then
        GetZoneCount = GetZoneCount + 1
    End If
Next

End Function

Open in new window

ProfessorJimJamMicrosoft Excel ExpertCommented:
please find attached solution with only formula
EE.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Martin Liss, I don't know how to use that UDF. I need to have the result in a cell.
Martin LissOlder than dirtCommented:
Here's how you add the UDF.

In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the UDF (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

After that you can use it just like a normal formula.
ProfessorJimJamMicrosoft Excel ExpertCommented:
Euro5

did you check my attachment? is there any problem with that?
Euro5Author Commented:
Perfect thanks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.