Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Function

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Site Certified operator
    1 yes
    yes
    no
    2 yes
    no
    3 no
    yes
    4 no
    no

    Can anyone come up with a function that will count how many of the sites in column one have at least one "Yes" in the corresponding certified operator cells?

    Sites can have multiple operators but any combination could be certified. If one site has 3 certified operators it should only count 1.

    Answer for above example would be 3.

    I've been struggling with this one for awhile, any help is greatly appreciated.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just need to know for sure if
    "A" is correct or "B" See Below
    Please Reply

    "A"
    Site Certified operator
    1 yes
    yes
    no
    2 yes
    no
    3 no
    yes
    4 no
    no


    "B"
    Site Certified operator
    1
    yes
    yes
    no
    2
    yes
    no
    3
    no
    yes
    4
    no
    no


    Are you familiar with macros???
    If not which Row does this list begin in?
    Which Column?

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Column 1____Column 2
    1___________yes
    ____________yes
    ____________no
    2___________yes
    ____________no
    3___________no
    ____________yes
    4___________no
    ____________no

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you familiar with macros?
    Your problem is easy to solve, but
    I need to know if I can skip all the details with my next reply.

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No I'm not very familiar with macros. I'm fairly new to this, I was trying to create a function involving arrays that would solve it.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,649
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-21 06:08, SB wrote:
    Site Certified operator
    1 yes
    yes
    no
    2 yes
    no
    3 no
    yes
    4 no
    no

    Can anyone come up with a function that will count how many of the sites in column one have at least one "Yes" in the corresponding certified operator cells?

    Sites can have multiple operators but any combination could be certified. If one site has 3 certified operators it should only count 1.

    Answer for above example would be 3.

    I've been struggling with this one for awhile, any help is greatly appreciated.
    Consider your sample (added an additional row) to be A2:C6.

    {1,"yes","no";
    2,"yes","no";
    3,"no","yes";
    4,"no","no";
    5,"yes","yes"}

    What follows will give you the desired count:

    =SUMPRODUCT((1*((B2:B6="yes")+(C2:C6="yes"))))-SUMPRODUCT((B2:B6="yes")*(C2:C6="yes"))

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    It's not a good idea to open and run a macro from someone you do not know but I'll send it to you anyway in case you want to use it.

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, the formula works and is close to what I need. However, the information I have is listed differently then you have it. Sorry, I was probably not clear initally.

    You wrote the data from A2:C6 as;

    1____yes____no
    2____yes____no
    3____no_____yes
    4____no_____no
    5____yes____yes

    When in fact it is from A2:B11;

    1____yes
    _____no
    2____Yes
    _____no
    3____no
    _____yes
    4____no
    _____no
    5____yes
    _____yes

    Each number (1 to 5) can have any number of 'yes' or 'no' responses listed vertically. The formula needs to count only 1 if any number of 'yes' is found in the responses for each number.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Here is the Macro for you
    Since you do not know how to work with macros, you will have to follow these direction carefully or find someone to edit it for you if necc.

    List your Site numbers in Column A beginning on Row #2

    List the Yes/No data in Column B beginning with Row #2

    From Excel hit Alt/F11 to open the Visual Basic Environment

    Click on 'Insert' on your Menu Bar up top

    Copy everything Between the ################
    and paste it exactly as is

    close the Visual Basic Environment and From Excel hit Alt/F8 to open the macro box.

    Choose "Yes_No_macro" and click on run.

    Make sure you are viewing the sheet which you are wanting to process

    Will list all sites with a certified operator in column "C"

    Will give you the total number of certified operators in column "D"

    Hope this helps

    Copy below between '#########################

    '##############################################################################
    Sub Yes_No_macro()
    Dim X
    Dim Y
    Dim Z
    Dim PutInC1
    Dim Certified As Boolean
    Dim CurrentSite
    PutInC1 = 2
    Z = 1
    Range("A1").Value = "Site Numbers"
    Range("B1").Value = "Certified?"
    For X = 3 To 65500
    Certified = False
    If Range("A" & X).Value > 0 Then
    CurrentSite = Range("A" & X).Value
    Y = X + 1
    If LCase(Trim(Range("B" & X).Value)) = "yes" Then _
    Certified = True
    Do Until Range("A" & Y).Value > 0
    If Y > 65500 Then Exit Do
    If LCase(Trim(Range("B" & Y).Value)) = "yes" Then
    Certified = True
    End If
    Y = Y + 1
    Loop
    X = Y - 1
    If Certified = True Then
    Z = Z + 1
    PutInC1 = PutInC1 + 1
    Range("C1").Value = "Cite Certified List"
    Range("C" & PutInC1).Value = CurrentSite
    End If
    End If
    Next
    Done:
    Range("D1").Value = "Number of Sites with Certified Operators = " & Z - 1

    End Sub
    '##############################################################################

    [ This Message was edited by: TsTom on 2002-03-21 07:36 ]

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow. I never new excel could do anything like that, I never would have figured that out. Thanks a lot. I have a lot to learn.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •