VBA to calculate Average with multiple criteria in same criteria range (String Cateria)

[FaridWahidi]

Hi,

I am a bit confused what is the accurate approach to be used in calculating average with multiple string criteria in the same criteria range.

I have try AverageIfs but the output is totally wrong

Sub Calculate()


Dim JobPosition As Variant
Dim rng_1, rng_2, rng_3, rng_4 As Range
Dim ws As Worksheet


JobPosition = Array("Officer", "Executive")


    Set ws = Worksheets("Score")
    
    With Application.WorksheetFunction
    
        For i = LBound(JobPosition) To UBound(JobPosition)
        
          ws.Range("C4").Value = ws.Range("C4").Value + .CountIfs(rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(i))
          ws.Range("D4").Value = ws.Range("D4").Value + .SumIfs(rng_3, rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(i))
          ws.Range("F4").Value = ws.Range("F4").Value + [COLOR=#ff0000].AverageIfs[/COLOR](rng_4, rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(i))


        Next i 
    End With
End Sub

 

[AlphaFrog]

The range variables are declared at the top of the code,
Dim rng_1, rng_2, rng_3, rng_4 As Range

But they are not set in the code. What are the ranges?

Also the way the ranges are declared, only rng_4 is type-Range. The other three are type-Variant.
This declares all four as type-Range

Dim rng_1 as Range, rng_2 as Range, rng_3 as Range, rng_4 As Range

 

[Worf]

Hi Farid
Could you post a sample of your data and the expected results? You may use Excel Jeanie to paste ranges here.
The following code executes, but I can’t tell if the results will be meaningful to you.

Sub Calculate()
Dim JobPosition, i%
Dim rng_1 As Range, rng_2 As Range, rng_3 As Range, rng_4 As Range, ws As Worksheet
Set ws = Worksheets("Score")
JobPosition = Array("Officer", "Executive")
Set rng_1 = ws.Range("a2:a12")
Set rng_2 = Range("e2:e12")
Set rng_4 = Range("b2:b12")
Set rng_3 = Range("g2:g12")
With Application.WorksheetFunction
    For i = LBound(JobPosition) To UBound(JobPosition)
      ws.Range("C4").Value = ws.Range("C4").Value + .CountIfs(rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(i))
      ws.Range("D4").Value = ws.Range("D4").Value + .SumIfs(rng_3, rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(i))
      ws.Range("F4").Value = ws.Range("F4").Value + .AverageIfs(rng_4, rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(i))
    Next
End With
End Sub

 

[FaridWahidi]

Hi AlphaFrog,
Thanks for advised me pertaining to wrong method of variable declaration.


Hi Worf,
Here is my complete code. My concern is about VBA Code to calculate Average with multiple criteria (String Criteria) in the same criteria range. It works for for CountIfs and SumIfs but we can never calculate Average of 2 different criteria separately.


In the meantime, if the criteria are numeric I can use like .AverageIfs(rng_4, rng_3, "> 1", rng_3, "<=4") but it definitely not applicable for string. one more thing, in excel formula we can use like SumProduct when dealing with multiple string criteria but it not works in VBA code.


Here is my sample of workbook to be downloaded.
https://app.box.com/s/liohuf16ryyoxqf7mt8m


The expected result of average should be 3.71.

Sub JobPosition()
Dim JobPosition As Variant
Dim rng_1 As Range, rng_2 As Range, rng_3 As Range, rng_4 As Range
Dim i As Long, j As Long, a As Long


    Worksheets("ORIGINATOR").Select
           i = Range("G" & Rows.count).End(xlUp).Row
           j = Range("G9:G" & Rows.count).End(xlDown).Row
        
    Set ws = Worksheets("Score")
    Set rng_1 = Worksheets("ORIGINATOR").Range("B" & j & ":B" & i)   'Sector Group
    Set rng_2 = Worksheets("ORIGINATOR").Range("AB" & j & ":AB" & i) 'Total AAs
    Set rng_3 = Worksheets("ORIGINATOR").Range("F" & j & ":F" & i)   'Job Position
    Set rng_4 = Worksheets("ORIGINATOR").Range("SZ" & j & ":SZ" & i) 'Cumulative Average Score (CAS) December


JobPosition = Array("Officer", "Executive")


    With Application.WorksheetFunction
        For a = LBound(JobPosition) To UBound(JobPosition)
        
          ws.Range("C4").Value = ws.Range("C4").Value + .CountIfs(rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(a))
          ws.Range("D4").Value = ws.Range("D4").Value + .SumIfs(rng_2, rng_1, "Global Banking", rng_3, JobPosition(a))
          ws.Range("F4").Value = ws.Range("F4").Value + .AverageIfs(rng_4, rng_1, "Global Banking", rng_3, JobPosition(a))
        Next a
    End With
End Sub

 

[FaridWahidi]

Sorry,

Here is the correct link
https://app.box.com/s/hugb7qnlel3tn2k7lxhg

 

[joeu2004]

The expected result of average should be 3.71.

Sub JobPosition()
Dim JobPosition As Variant
Dim rng_1 As Range, rng_2 As Range, rng_3 As Range, rng_4 As Range
Dim i As Long, j As Long, a As Long

    Worksheets("ORIGINATOR").Select
           i = Range("G" & Rows.count).End(xlUp).Row
           j = Range("G9:G" & Rows.count).End(xlDown).Row
        
    Set ws = Worksheets("Score")
    Set rng_1 = Worksheets("ORIGINATOR").Range("B" & j & ":B" & i)   'Sector Group
    Set rng_2 = Worksheets("ORIGINATOR").Range("AB" & j & ":AB" & i) 'Total AAs
    Set rng_3 = Worksheets("ORIGINATOR").Range("F" & j & ":F" & i)   'Job Position
    Set rng_4 = Worksheets("ORIGINATOR").Range("SZ" & j & ":SZ" & i) 'Cumulative Average Score (CAS) December

JobPosition = Array("Officer", "Executive")

    With Application.WorksheetFunction
        For a = LBound(JobPosition) To UBound(JobPosition)
        
          ws.Range("C4").Value = ws.Range("C4").Value + .CountIfs(rng_1, "Global Banking", rng_2, ">0", rng_3, JobPosition(a))
          ws.Range("D4").Value = ws.Range("D4").Value + .SumIfs(rng_2, rng_1, "Global Banking", rng_3, JobPosition(a))
          ws.Range("F4").Value = ws.Range("F4").Value + .AverageIfs(rng_4, rng_1, "Global Banking", rng_3, JobPosition(a))
        Next a
    End With
End Sub

First, some details:

1. It would be prudent to include the declaration: Dim ws As Worksheet. But this is not the cause of any error.

2. You neglect to initialize C4, D4 and F4 of sheet Score to zero. This is a problem if we execute the macro multiple times. Add the following statements outside the loop:
ws.Range("c4")=0
ws.Range("d4")=0
ws.Range("f4")=0

Those details (even #2) are not the cause of your error.

WorksheetFunction.AverageIfs is working just fine. It returns about 7.16 = 108.06/29 + 6.89/2.

The problem is: mathematically, the correct average is about 3.71 = (108.06 + 6.89) / (29 + 2).

Note: 108.06 and 6.89 are rounded approximations of the actual values.

The following code is one way to calculate the correct average:

    Dim mysum As Double, mycount As Long
    [....]
    mysum = 0: mycount = 0
    With Application.WorksheetFunction
        For a = LBound(JobPosition) To UBound(JobPosition)
          [....]
          mysum = mysum + .SumIfs(rng_4, rng_1, "Global Banking", rng_3, JobPosition(a))
          mycount = mycount + .CountIfs(rng_4, ">0", rng_1, "Global Banking", rng_3, JobPosition(a))
        Next a
    End With
    ws.Range("F4").Value = mysum / mycount

 

[FaridWahidi]

Hi joue2004,

I forgot I have used SUMIFS/COUNTIFS in excel formula before. I am fully aware that the AverageIfs is working fine but mathematically wrong output. Actually, I am trying to find is there any special VBA function which can return the result with only one execution. e.g SUMPRODUCT in excel formula


The code posted above only a small part from my actual VBA code. I forgot to copy the declaration ws as Worksheet. As for setup variable to be start with zero is also not require because I am using create copy function and delete existing worksheets

            Application.DisplayAlerts = [COLOR=#0000cd]False[/COLOR]
[COLOR=#0000cd]            On Error Resume Next[/COLOR]
            ThisWorkbook.Sheets("Score").Delete
[COLOR=#0000cd]            On Error GoTo 0[/COLOR]
            Application.DisplayAlerts = [COLOR=#0000ff]True[/COLOR]

Worksheets("Template_Score").Visible = [COLOR=#0000cd]True[/COLOR]
Worksheets("Template_Score").Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.count)
ActiveSheet.Name = "Score"
Worksheets("Template_Score").Visible = [COLOR=#0000cd]False[/COLOR]

Anyway thanks a lot.