IF & SEARCH statements in Excel '97

[willhyde]

I have a list of addresses in cells A1:A200 in the format

66 Any Street, Somewhere, ABC123

and in cells B1:B200 the response to a question - 'yes', 'no' or 'don't care'.

I need to count how many people who live in "Any" street said "yes" to the question. I suspect I need a statement saying

COUNTIF((B1:B200,&quot;yes&quot AND A1:A200 <contains>&quot;Any&quot

which will return a number.

I just don't know the appropriate command for <contains>.

Any Ideas?

Will

 

[ETID]

you could add the code below as a custom function, but it will only work on the machine that you add it to.

if you need help on add-ins and custom functions write back, it's fairly easy to do

then in your spreadsheet you would type

=countifboth(range(1),range(2),criteria of range(1),criteria of range(2))

example:
=countifboth(a1:a10,c1:c10,&quot;yes&quot;,&quot;any&quot

-----------------------------------------------------

Function Countifboth(InRange1, Inrange2, criteria1, criteria2)
If criteria1 = &quot;&quot; Then Beep: End
Dim r As Long
Dim x As Integer
Dim lastr1 As Long
Dim lastr2 As Long
Dim in_range1(70000)
Dim in_range2(70000)

Set SubSetRange1 = _
Intersect(InRange1.Parent.UsedRange, InRange1)
Set SubSetRange2 = _
Intersect(Inrange2.Parent.UsedRange, Inrange2)
thecount = 0
For Each cell In SubSetRange1
in_range1(r) = cell.Value
r = r + 1
Next cell
lastr1 = r: r = 0
For Each cell In SubSetRange2
in_range2(r) = cell.Value
r = r + 1
Next cell
lastr2 = r: r = 0
If lastr1 <> lastr2 Then Beep: MsgBox (&quot;Both ranges must have equal rows!&quot: End

Do While r < lastr1
If in_range1(r) = criteria1 And in_range2(r) = criteria2 Then thecount = thecount + 1
r = r + 1
Loop
Countifboth = thecount

End Function

 

[logius]

You could include the code with the spreadsheet by adding it as a module. Just open the workbook, hit Alt+F11 and you should see the VB editor open up. Right-click on the workbook and select Insert>Module. Cut and paste the code into the open window and save the document. From then on, anyone who uses the workbook will have access to your function. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com