mardi 25 avril 2017

VBA AverageIfs application with OR condition

My data is placed in a range. How do i get an average of a column, with multiple conditions herof an OR condition. in this example Temprange.columns(2) should be "High" or "Major"

This should be done without taking an average of two averages as per below example:

Dim a As Long
Dim b As Long
Dim ObjectKeyCounter As Long
Dim TempRange As Range
Dim TempArr As Variant
Dim MyArray As Variant

a = Application.WorksheetFunction.AverageIfs(TempRange.Columns(20), _
TempRange.Columns(1), TempArr(ObjectKeyCounter), TempRange.Columns(2), "High")
b = Application.WorksheetFunction.AverageIfs(TempRange.Columns(20), _
TempRange.Columns(1), TempArr(ObjectKeyCounter), TempRange.Columns(2), "Major")
MyArray(1, 1) = Application.WorksheetFunction.Average(a, b)

For some reason i can't find anything on the internet so i guess my way around it (and thereby my search) is wrong.

Aucun commentaire:

Enregistrer un commentaire