VBA Formulas

[GordonP]

Is is possible to write a formula using VBA?

I need to use a countif whereby a cell equals a value and then count two options in a different cell.

For example

Cell A
Account Executive

Cell B
1 or 2

Can you write a countif formula using VBA that counts Cell A each time Account Executive appears along with how many times 1 or 2 appears in cell B?

Hope this makes sense!

Thanks

[RichardSchollar]

Hi Gordon

Do you mean you want to write this formula into a third cell (eg cell C1) in the worksheet using VBA, or you want to calculate the result of such a formula within VBA (eg to then make choices about what your VBA code then does)?

Richard

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim tmp As Variant

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, TEST_COLUMN).Value <> tmp Then

.Cells(i, "C").Formula = "=SUMPRODUCT(--(A1:A" & LastRow & "=A" & i & "),--(B1:B" & LastRow & "=1))"
.Cells(i, "D").Formula = "=SUMPRODUCT(--(A1:A" & LastRow & "=A" & i & "),--(B1:B" & LastRow & "=2))"
tmp = .Cells(i, TEST_COLUMN).Value
End If
Next i

End With

End Sub

[GordonP]

Hi Gordon,

Do you mean you want to write this formula into a third cell (eg cell C1) in the worksheet using VBA, or you want to calculate the result of such a formula within VBA (eg to then make choices about what your VBA code then does)?

Richard

yes, I woould like to write the formula into a 3rd cell. On the actual sheet the data "Account Executive" appears in column C, the 1 or the 2 appears in column AI, so I would like to countif the number of times "Account Executve" appears with a 1 or 2 in column AI. There is also two other types of data "Service Centre" or "Internet" that appears in column C that I would like to count the number of times 1 or 2 appears in colum AI.

Cheers
Gordon

P.S XLD - Thanks for your response, how would I make this more relevant taking into account the information above?

[Bob Phillips]

Is the Account Executive a name, or does it say exactly that?

[GordonP]

Hi XLD,

In column C, the data will appear as "Account Executive", "Service Centre" or "Internet". They are not names but will appear as above. In column AI the data range will be between 0-5. I need to count the number of times a 1 or 2 appears for "Account Executive", "Service Centre" or "Internet".

Hope this makes sense!

Cheers
GordonP

[Simon Lloyd]

Gordon, xld has probably misunderstood what you were asking as i may have it's not entirely clear what you want, the code below (somewhat crude!) will count the things you want and place the results in columns c & d:
Sub Count_Services()
Dim Rng As Range, MyCell As Range
Dim i As Integer
Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
i = 0
For Each MyCell In Rng
If MyCell.Value = "Account Executive" And MyCell.Offset(0, 1) = 1 Then
i = i + 1
End If
Next
Range("C1").Value = "AE's 1 Count = " & i
i = 0
For Each MyCell In Rng
If MyCell.Value = "Account Executive" And MyCell.Offset(0, 1) = 2 Then
i = i + 1
End If
Next
Range("D1").Value = "AE's 2 Count = " & i
i = 0
For Each MyCell In Rng
If MyCell.Value = "Account Executive" And MyCell.Offset(0, 1) = 1 Then
i = i + 1
End If
Next
Range("C2").Value = "Service centre's 1 Count = " & i
i = 0
For Each MyCell In Rng
If MyCell.Value = "Service Centre" And MyCell.Offset(0, 1) = 2 Then
i = i + 1
End If
Next
Range("D2").Value = "Service centres's 2 Count = " & i
i = 0
For Each MyCell In Rng
If MyCell.Value = "Internet" And MyCell.Offset(0, 1) = 1 Then
i = i + 1
End If
Next
Range("C3").Value = "Internets's 1 Count = " & i
i = 0
For Each MyCell In Rng
If MyCell.Value = "Internet" And MyCell.Offset(0, 1) = 2 Then
i = i + 1
End If
Next
Range("D3").Value = "Internets's 2 Count = " & i
Columns("C:D").AutoFit
End Subor did you want a running count next to each instance of your key words? please explain some more.

[Bob Phillips]

=SUMPRODUCT(--($C$2:$C$200="Account Executive"),--($AI$2:$AI$200=1))

=SUMPRODUCT(--($C$2:$C$200="Account Executive"),--($AI$2:$AI$200=2))

=SUMPRODUCT(--($C$2:$C$200="Service Centre"),--($AI$2:$AI$200=1))

etc.