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

FaridWahidi

Board Regular
Joined
Apr 22, 2014
Messages
93
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

Code:
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
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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.
Code:
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
 
Last edited:
Upvote 0
The expected result of average should be 3.71.
Code:
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:
Code:
    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
 
Upvote 0
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
Code:
            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. :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top