Macro for AverageIFS, with multiple criteria in the same criteria range

Hi,

Can onyone help me on how to create AVERAGEIFS with multiple criteria in the same criteria range

e.g. January, February, March, etc.

Sub a()
Dim WhatToAvg As Range, Criteria1_Range, Criteria2_Range As Range
Dim calculate As Variant
 
Set WhatToAvg = Range("H524:IW581")
Set Criteria1_Range = Range("H515:IW572")
Set Criteria2_Range = Range("H519:IW576")

calculate = Application.WorksheetFunction.AverageIfs(WhatToAvg, Criteria1_Range, "Less Complex", Criteria2_Range, "January")

MsgBox Format(calculate, "0.00")
End Sub

Hi,

I have tried this code but still not working. Please help me

Sub a1()
Dim WhatToAvg As Range, Criteria1_Range, Criteria2_Range As Range
Dim calculate As Variant
Dim iCriteria As String
Dim longVar As Long
 
Set WhatToAvg = Range("H524:IW581")
Set Criteria1_Range = Range("H515:IW572")
Set Criteria2_Range = Range("H519:IW576")
iCriteria = Array("January", "February", "March")

For longVar = LBound(iCriteria) To UBound(iCriteria)
calculate = Application.WorksheetFunction.AverageIfs(WhatToAvg, Criteria1_Range, "Less Complex", Criteria2_Range, iCriteria(longVar))
Next longVar

MsgBox Format(calculate, "0.00")

End Sub

Hi,

Anyone please help me on this. I have tried many times but still got error.

Thanks.
Farid

HI marvinP,

I have already replied that your formula works but what i want still not working

AVERAGE(IF((H515:IW572=H503)*(H519:IW576={"January","February"},H524:IW581))) CRTL + SHIFT + ENTER


I have also have tried the VBA code as above, do you know what went wrong on the #2 post ? please help. Many thanks for willing to assist.

Hi,

Anybody who are expert, please assist me.

I have tried many methods, but still not working.


Regards,
Farid.

Hi xladept,

Attached herewith the sample of workbook in a small range. Here is VBA code which has error on 2nd the calculation

Sub Average_Ifs()
Dim WhatToAvg As Range, Criteria1_Range, Criteria2_Range As Range
Dim calculate1, calculate2 As Variant
 
Set WhatToAvg = Range("H26:Z26")
Set Criteria1_Range = Range("H17:Z17")
Set Criteria2_Range = Range("H21:Z21")


calculate1 = Application.WorksheetFunction.AverageIfs(WhatToAvg, Criteria1_Range, "Less Complex", Criteria2_Range, "January")

'Does not work
calculate2 = Application.WorksheetFunction.AverageIfs(WhatToAvg, Criteria1_Range, "Less Complex", Criteria2_Range, "January" & "February" & "March")

Debug.Print calculate1
Debug.Print calculate2

End Sub

In the meantime, only one of my Excel formula work & another one not

This one working fine
Where, M4 = January, M5 = February, M6 = March

=AVERAGE(IF((H17:Z17=H5)*((H21:Z21=M4)+(H21:Z21=M5)+(H21:Z21=M6)),H26:Z26)) CRTL + SHIFT + ENTER


The following not working
=AVERAGE(IF((H17:Z17=H5)*((H21:Z21={"January","February","March"})),H26:Z26)) CRTL + SHIFT + ENTER



Please help me, Thanks a lot

Regards
Farid

Hi Xladept,

Have you open the attachment to look into this matter?

If can't, kindly let me know, I will attach it again. Thanks


Regards,
Farid

Hi Xladept,

Thanks for reply.


Hi All,

Can you help me too.

Regards,
Farid

Hi Sixthsense,

Thanks for willing to assist me.

The result should be 3.53 instead of 0.

Please refer to my attachment.

Regards,
Farid

Hi Sixthsense,

Based on the attachment, my problem is to calculate average in multiple column. If averageifs unable to accept multiple Column range as criteria range, what is alternative method to count average of multiple column in VBA?


As for the excel fomula I found this, but there is no special VBA code (Application.WorksheetFunction.XXXX) to run in VBA.

=AVERAGE(IF((H17:Z17=H5)*((H21:Z21=M4)+(H21:Z21=M5)+(H21:Z21=M6)),H26:Z26)) CRTL + SHIFT + ENTER

Where, M4 = January, M5 = February, M6 = March


Regards,
Farid

Hi Sixthsense,

It has return 0 value.

When I put FormulaArray, it has returned false.

Sub GetAvgArr()
    MsgBox Evaluate(FormulaArray = "=AVERAGE(IF((H17:Z17=H5)*((H21:Z21=M4)+(H21:Z21=M5)+(H21:Z21=M6)),H26:Z26))")
End Sub

Hi,

Finally, It works.

Thanks.