i have some data as follows

PLACE id number
Offshore 1
Onsite 10
Onsite 11
Onsite 12
Offshore 2
Onsite 12
Offshore 3
Offshore 3
Onsite 13
Offshore 4
Offshore 5
Onsite 14
Offshore 4
Offshore 6
Offshore 6
Offshore 2
Offshore 7



please tell me how do i count distinct number of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....

IF??? I understand your desires, look in the help index for COUNTIF

--
Don Guillett
SalesAid Software

"rishi" wrote in message
ups.com...

i have some data as follows

PLACE id number
Offshore 1
Onsite 10
Onsite 11
Onsite 12
Offshore 2
Onsite 12
Offshore 3
Offshore 3
Onsite 13
Offshore 4
Offshore 5
Onsite 14
Offshore 4
Offshore 6
Offshore 6
Offshore 2
Offshore 7



please tell me how do i count distinct number of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....

thanks... but i cant find a way for this.... can u pls elaborate the
method...



On May 10, 8:00 pm, "Don Guillett" wrote:
IF??? I understand your desires, look in the help index for COUNTIF

--
Don Guillett
SalesAid Software
"rishi" wrote in message

ups.com...





i have some data as follows

PLACE id number
Offshore 1
Onsite 10
Onsite 11
Onsite 12
Offshore 2
Onsite 12
Offshore 3
Offshore 3
Onsite 13
Offshore 4
Offshore 5
Onsite 14
Offshore 4
Offshore 6
Offshore 6
Offshore 2
Offshore 7

please tell me how do i countdistinctnumber of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....- Hide quoted text -

- Show quoted text -

Select your "on/off" column data and then run this.

Tim

Sub Count()

Dim id, c
Dim onD As Object, offD As Object
Set onD = CreateObject("scripting.dictionary")
Set offD = CreateObject("scripting.dictionary")


For Each c In Selection
id = c.Offset(0, 1).Value
If c.Value = "Onsite" Then
If Not onD.Exists(id) Then onD(id) = "y"
ElseIf c.Value = "Offshore" Then
If Not offD.Exists(id) Then offD(id) = "y"
Else
'handle these ?
End If
Next c

MsgBox "On: " & onD.Count & vbCrLf & _
"Off: " & offD.Count


End Sub




i have some data as follows

PLACE id number
Offshore 1
Onsite 10
Onsite 11
Onsite 12
Offshore 2
Onsite 12
Offshore 3
Offshore 3
Onsite 13
Offshore 4
Offshore 5
Onsite 14
Offshore 4
Offshore 6
Offshore 6
Offshore 2
Offshore 7

please tell me how do i countdistinctnumber of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....- Hide quoted text -

- Show quoted text -

hey thanks a lot.... that code got my job done...


On May 11, 10:30 am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Select your "on/off" column data and then run this.

Tim

Sub Count()

Dim id, c
Dim onD As Object, offD As Object
Set onD = CreateObject("scripting.dictionary")
Set offD = CreateObject("scripting.dictionary")

For Each c In Selection
id = c.Offset(0, 1).Value
If c.Value = "Onsite" Then
If Not onD.Exists(id) Then onD(id) = "y"
ElseIf c.Value = "Offshore" Then
If Not offD.Exists(id) Then offD(id) = "y"
Else
'handle these ?
End If
Next c

MsgBox "On: " & onD.Count & vbCrLf & _
"Off: " & offD.Count

End Sub





i have some data as follows

PLACE id number
Offshore 1
Onsite 10
Onsite 11
Onsite 12
Offshore 2
Onsite 12
Offshore 3
Offshore 3
Onsite 13
Offshore 4
Offshore 5
Onsite 14
Offshore 4
Offshore 6
Offshore 6
Offshore 2
Offshore 7

please tell me how do i countdistinctnumber of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

1.. Create a blank workbook or worksheet.
2.. Select the example in the Help topic. Do not select the row or column
headers.
From HELP

Selecting an example from Help

3.. Press CTRL+C.
4.. In the worksheet, select cell A1, and press CTRL+V.
5.. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the Tools menu, point
to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
A B
Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description (Result)
=COUNTIF(A2:A5,"apples") Number of cells with apples in the
first column above (2)
=COUNTIF(B2:B5,"55") Number of cells with a value greater than
55 in the second column above (2)



--
Don Guillett
SalesAid Software

"rishi" wrote in message
ups.com...
thanks... but i cant find a way for this.... can u pls elaborate the
method...



On May 10, 8:00 pm, "Don Guillett" wrote:
IF??? I understand your desires, look in the help index for COUNTIF

--
Don Guillett
SalesAid Software
"rishi" wrote in
message

ups.com...





i have some data as follows

PLACE id number
Offshore 1
Onsite 10
Onsite 11
Onsite 12
Offshore 2
Onsite 12
Offshore 3
Offshore 3
Onsite 13
Offshore 4
Offshore 5
Onsite 14
Offshore 4
Offshore 6
Offshore 6
Offshore 2
Offshore 7

please tell me how do i countdistinctnumber of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....- Hide quoted text -

- Show quoted text -