Hi there,
I want to create a formula using vba for counting how many calls being made by the user.
Please find the attached for your reference.
Many thanks in advance.
Rgds,
harto
Hi there,
I want to create a formula using vba for counting how many calls being made by the user.
Please find the attached for your reference.
Many thanks in advance.
Rgds,
harto
I'm not clear if you want a Count or an Average. Either CountIf or AverageIf would seem to suffice though.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
What is wrong with your formula?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
This array formula is better
=IF(COUNTIF(A$4:$A5,A5)=1,0,AVERAGE(IF($A$4:$A5=$A5,$C$4:$C5)))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi Malcom,
Can you advise me to use Average function with VBA for this case.
User N made 2 calls (in total) then averaging by 2 Row. As result would be 2/2=1 (in average)
Appreciate your help.
Just add the formula via VBA
[vba]
Range("J5").FormulaArray = "=IF(COUNTIF(A$4:$A5,A5)=1,0,AVERAGE(IF($A$4:$A5=$A5,$C$4:$C5)))"
Range("J5").AutoFill Range("J5:J14")
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
HI Bob,
as the data retrieve by an Query, then I need a vba code to auto populate.
[VBA]Dim newrc As New ADODB.Recordset
Dim i As Long
Sheets("ReportActivity").Select
[a3:H3].Select
Selection.AutoFilter
Range(Cells(3, 1), Cells(3, 10)) = _
Array("USER NAME", "DATE", "#of CALL", "Make Appt", "%", "Effective Calls", "%", "In-Effective Calls", "%", "Ave calls")
Cells(3, 1).Resize(1, 10).Select
Selection.HorizontalAlignment = xlCenter
Call Connect
Range("A4").Select
Queryku = "select * from X_4_Final_Sum_CALL_And_Appt_Monthly"
i = 4
With newrc
Set RsCluster = Cn.Execute(Queryku)
If RsCluster.EOF Then
MsgBox "Done", vbNullString
Exit Sub
End If
.Open Queryku, Cn, adOpenDynamic, adLockOptimistic, adCmdText
Do While Not .BOF And Not .EOF
Cells(i, 1).Value = Trim(!UserName)
Cells(i, 2).Value = Trim(!Year) & Trim(!Month) & Trim(!Date)
Cells(i, 3).Value = Format(![Total Call], "#,##0")
Cells(i, 4).Value = Format(![ApptDate], "#,##0")
Cells(i, 5).Formula = Format(Cells(i, 4).Value / Cells(i, 3).Value, "0%")
Dim NRow As Long
NRow = Cells(Rows.Count, "A").End(xlUp).row - 2
With Range("F4").Resize(NRow)
.Formula = "=SUMPRODUCT(--(RC[-5]=DetailsA),--(RC[-4]=DetailsB),--(""Contacted""=DetailsD))"
End With
With Range("H4").Resize(NRow)
.Formula = "=SUMPRODUCT(--(RC[-7]=DetailsA),--(RC[-6]=DetailsB),--(""UnContacted""=DetailsD))"
End With
Cells(i, 7).Formula = Format(Cells(i, 6).Value / Cells(i, 3).Value, "0%")
Cells(i, 9).Formula = Format(Cells(i, 8).Value / Cells(i, 3).Value, "0%")
[/VBA]
Wow, thanks for quick reply, Bob
ok. will try first.