# Count if cell contains data

#### 2k05gt

##### Board Regular
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

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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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,

Code:
``````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``````

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,

Code:
``````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

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?

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?

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

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

is there a way to do the command ?

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

### Forum statistics

1,206,808
Messages
6,074,990
Members
446,112
Latest member
nmz1133 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back