I need to count the number of disconnects, "D"s, in for each employee in the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would be
located. The formula will be on a different sheet than the data and copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
..
..
13 Employee2
14 Month
15 WO#
16 Type

this formula on sheet2 will count the number of D's in row 11 if row 9 has a 1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would be
located. The formula will be on a different sheet than the data and copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

Oh, thank you! This did calucate my first employee. Now, I need to copy the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9 has a 1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would be
located. The formula will be on a different sheet than the data and copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

what row and column will contain these formulas on the 2nd page?

--


Gary


"St@cy" wrote in message
...
Oh, thank you! This did calucate my first employee. Now, I need to copy the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9 has a
1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would be
located. The formula will be on a different sheet than the data and copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

maybe you can adapt something from this. change the sheet references and the
location for the formulas, i have them starting on sheet2 cell A1.

Option Explicit

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 1

For i = 9 To lastrow Step 5
ws2.Range("A" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i & ":IV" &
i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"St@cy" wrote in message
...
Oh, thank you! This did calucate my first employee. Now, I need to copy the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9 has a
1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would be
located. The formula will be on a different sheet than the data and copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

J3 for Employee 1
J4 for Employee 2
..
..
etc.

"Gary Keramidas" wrote:

what row and column will contain these formulas on the 2nd page?

--


Gary


"St@cy" wrote in message
...
Oh, thank you! This did calucate my first employee. Now, I need to copy the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9 has a
1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would be
located. The formula will be on a different sheet than the data and copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

here you a

Option Explicit

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 3

For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i & ":IV" &
i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"St@cy" wrote in message
...
J3 for Employee 1
J4 for Employee 2
.
.
etc.

"Gary Keramidas" wrote:

what row and column will contain these formulas on the 2nd page?

--


Gary


"St@cy" wrote in message
...
Oh, thank you! This did calucate my first employee. Now, I need to copy
the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9 has
a
1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in
the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would
be
located. The formula will be on a different sheet than the data and
copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

hopefully this one won't wrap

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 3

For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i & ":IV" & i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
here you a

Option Explicit

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 3

For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i & ":IV"
& i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"St@cy" wrote in message
...
J3 for Employee 1
J4 for Employee 2
.
.
etc.

"Gary Keramidas" wrote:

what row and column will contain these formulas on the 2nd page?

--


Gary


"St@cy" wrote in message
...
Oh, thank you! This did calucate my first employee. Now, I need to copy
the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9
has a
1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in
the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would
be
located. The formula will be on a different sheet than the data and
copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

I change the sheet names, but this appears to be a procedure. I think I need
a function.?? =DisconnectCount(MonthSelected)

Sub DisconnectCount(MonthSelected)
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("QA Input")
Set ws2 = Worksheets("Monthly Report")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).row
z = 3
For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--('QA Input'!E" & i & ":IV" & i
& _
"=MonthSelected),--('QA Input'!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next
End Sub

"Gary Keramidas" wrote:

hopefully this one won't wrap

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 3

For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i & ":IV" & i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
here you a

Option Explicit

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 3

For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i & ":IV"
& i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"St@cy" wrote in message
...
J3 for Employee 1
J4 for Employee 2
.
.
etc.

"Gary Keramidas" wrote:

what row and column will contain these formulas on the 2nd page?

--


Gary


"St@cy" wrote in message
...
Oh, thank you! This did calucate my first employee. Now, I need to copy
the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9
has a
1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee in
the
month of x. Each employee info takes a total of five lines. The row
indicating the month is two lines above the type, where the "D"s would
be
located. The formula will be on a different sheet than the data and
copied
down the column. Please help me with the right functions and/or macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type

you can either repost here or better yet, post in the excel function newsgroup.
this is generally for programming solutions, though the function experts read
here, too.

--


Gary


"St@cy" wrote in message
...
I change the sheet names, but this appears to be a procedure. I think I need
a function.?? =DisconnectCount(MonthSelected)

Sub DisconnectCount(MonthSelected)
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("QA Input")
Set ws2 = Worksheets("Monthly Report")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).row
z = 3
For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--('QA Input'!E" & i & ":IV" & i
& _
"=MonthSelected),--('QA Input'!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next
End Sub

"Gary Keramidas" wrote:

hopefully this one won't wrap

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 3

For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i & ":IV" & i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
here you a

Option Explicit

Sub emp_formulas()
Dim ws As Worksheet, ws2 As Worksheet
Dim i As Long, z As Long
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("sheet2")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
z = 3

For i = 9 To lastrow Step 5
ws2.Range("J" & z).Formula = "=SUMPRODUCT(--(Sheet1!E" & i &
":IV"
& i & _
"=1),--(Sheet1!E" & i + 2 & ":IV" & i + 2 & "=""D""))"
z = z + 1
Next

End Sub

--


Gary


"St@cy" wrote in message
...
J3 for Employee 1
J4 for Employee 2
.
.
etc.

"Gary Keramidas" wrote:

what row and column will contain these formulas on the 2nd page?

--


Gary


"St@cy" wrote in message
...
Oh, thank you! This did calucate my first employee. Now, I need to
copy
the
formula down the column with OFFSET some how in the mix.
Rows 9 & 11, 14 & 16, 19 & 21, .... See the pattern?

"Gary Keramidas" wrote:

this formula on sheet2 will count the number of D's in row 11 if row 9
has a
1
in it


=SUMPRODUCT(--(Sheet1!E9:IV9=1),--(Sheet1!E11:IV11="D"))
--


Gary


"St@cy" wrote in message
...
I need to count the number of disconnects, "D"s, in for each employee
in
the
month of x. Each employee info takes a total of five lines. The
row
indicating the month is two lines above the type, where the "D"s
would
be
located. The formula will be on a different sheet than the data and
copied
down the column. Please help me with the right functions and/or
macro.

Example:
'QA Input'
D E F .....IV
8 Employee1
9 Month 1 1 3
10 WO#
11 Type D D S
.
.
13 Employee2
14 Month
15 WO#
16 Type