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

#### FaridWahidi

##### Board Regular
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### AlphaFrog

##### MrExcel MVP
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:

#### Worf

##### Well-known Member
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``````

#### FaridWahidi

##### Board Regular
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.

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:

#### joeu2004

##### Banned user
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``````

• FaridWahidi

#### FaridWahidi

##### Board Regular
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]

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:

Replies
0
Views
911
Replies
7
Views
164
Replies
7
Views
482
Replies
1
Views
198
Replies
1
Views
120

### Forum statistics

1,191,580
Messages
5,987,440
Members
440,097
Latest member
Wint ### 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.

### Which adblocker are you using?    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

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