2 conditions Countif with offset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"))
 
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?
 
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
 
here you are:

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
 
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
 
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
 
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.
 
Back
Top