Count if cell contains data

[2k05gt]

I am trying to get a count if Video Confrence calls from one site to another

here is the code I am messing with
=COUNTIF(D2:D2135,"134.67.*")and, COUNTIF(B2:B2135, "161.23.*")
else COUNTIF(D2:D2135,"134.67.*")and, COUNTIF(B2:B2135, "162.23.*")


should result:
Calls from DC to atlanta 432
Calls from DC to London 343
Calls from HQ to Paris 32
Calls from HQ to Montrial 45

Spread Sheet:

4/21/2006 3:44:27 PM 204.46.33.22 161.80.11.22 DC 167 00:02:47 Outgoing Video H323 AES128 384 kbps TANDBERG 1000

 

[jss1234]

Are you looking to do this with VBA or exel formulas. You may consider a pivot table with counts and use formulas. If you are looking for VBA, please be more specific about the spreadsheet(s).

Thanks,

 

[jss1234]

Sub counting()
Dim a, b, c As Long
For c = 2 To 2135
    If Left(Range("D" & c).Value, 7) = "134.67." Then
        If Left(Range("B" & c).Value, 7) = "161.23." Then
            b = 1
        End If
    End If
    a = a + b
    b = 0
Next c
Range("F2").Value = "Calls from DC to Atlanta " & a
a = 0
For c = 2 To 2135
    If Left(Range("D" & c).Value, 7) = "134.67." Then
        If Left(Range("B" & c).Value, 7) = "162.23." Then
            b = 1
        End If
    End If
    a = a + b
    b = 0
Next c
Range("F3").Value = "Calls from DC to London " & a
a = 0

End Sub

 

[2k05gt]

Are you looking to do this with VBA or exel formulas. You may consider a pivot table with counts and use formulas. If you are looking for VBA, please be more specific about the spreadsheet(s).

Thanks,

VBA,
The Sheet is a log file that contains all call from and to devices based on IP addresses.

Network Address, Remote Site IP, Duration (sec), Duration, Call Direction, Call Type, Call Protocol, Encryption Mode, Bandwidth, Specific Type,

204.46.33.22, 161.80.11.22, 167 00:02:47, Outgoing, Video, H323, AES128, 384 kbps, TANDBERG 1000,

 

[2k05gt]

Dim a, b, c As Long
For c = 2 To 2135
    If Left(Range("B" & c).Value, 7) = "134.67." Then
        If Left(Range("D" & c).Value, 7) = "161.34." Then
            b = 1
        End If
    End If
    a = a + b
    b = 0
Next c
Range("C2140").Value = "Calls from SITE 1 to SITE 2 " & a
a = 0

This returns a 0 I tried to narrow it down by stepping through, it was counting but the final tally was 0

 

[jss1234]

In the log data, are there any preceeding text spaces, or anything like that - because the VBA is looking for the 7 text characters to the left of the cells which contain the 136.67.* data?

 

[jss1234]

Are the columns you listed above in the postin in the same order, because the VBA is also assuming that the to and from IP addresses are in column B and D?

 

[2k05gt]

Are the columns you listed above in the postin in the same order, because the VBA is also assuming that the to and from IP addresses are in column B and D?

EDIT

yea spaces are there, thanks

 

[jss1234]

You can test this by taking a blank sheet and typing in the data and the macro. it works fine when I try it.

 

[2k05gt]

is there a way to do the command ?

=COUNTIF(Left(range, (b2:b2135) "161.34.")