Function

[SB]

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.

[Tom Schreiner]

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?

[SB]

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

[Tom Schreiner]

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.

[SB]

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.

[Aladin Akyurek]

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"))

[Tom Schreiner]

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.

[SB]

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.

[Tom Schreiner]

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 ]

[SB]

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.