Averageif for multiple criteria in same column

[creative999]

How do i get multiple criteria in same column (column A) to work with averageif... example:

=AVERAGEIFS(Z2:Z500,P2:P500,"<>NEW",A2:A500,"APPROVED",A2:A500,"TEMPAPPROVED")

 

[Toadstool]

Hi Creative999,

AVERAGEIFS doesn't have an OR option. If column A APPROVED or TEMPAPPROVED are the only options containing APPROVED then you could use a wildcard (as in AA2) or you can change to using SUMPRODUCT (as in AA3).

Creative999.xlsx
	A														O	P										Z	AA
1	Status															Status2										Number	Results
2	APPROVED															NEW										5	16
3	APPROVED															OLD										15	16
4	TEMPAPPROVED																									17
5	Dog															Cat										35
6
Sheet1

Cell Formulas
Range	Formula
AA2	AA2	=AVERAGEIFS(Z2:Z500,P2:P500,"<>NEW",A2:A500,"*APPROVED")
AA3	AA3	=SUMPRODUCT((Z2:Z500)*((P2:P500<>"NEW")*((A2:A500="APPROVED")+(A2:A500="TEMPAPPROVED"))))/SUMPRODUCT((P2:P500<>"NEW")*((A2:A500="APPROVED")+(A2:A500="TEMPAPPROVED")))

 

[creative999]

Hi Creative999,

AVERAGEIFS doesn't have an OR option. If column A APPROVED or TEMPAPPROVED are the only options containing APPROVED then you could use a wildcard (as in AA2) or you can change to using SUMPRODUCT (as in AA3).

Thanks, I actually need two exclusions... For example,

=SUMPRODUCT((Z2:Z500)*((P2:P500<>"NEW")+(P2:P500<>"OLD")*((A2:A500="APPROVED")+(A2:A500="TEMPAPPROVED"))))/SUMPRODUCT((P2:P500<>"NEW")+(P2:P500<>"OLD")*((A2:A500="APPROVED")+(A2:A500="TEMPAPPROVED")))

But this gives a #VALUE! error.

 

[Fluff]

You have some of the brackets in the wrong place, try it like

=SUMPRODUCT((Z2:Z500)*((P2:P500<>"NEW")+(P2:P500<>"OLD"))*((A2:A500="APPROVED")+(A2:A500="TEMPAPPROVED")))/SUMPRODUCT(((P2:P500<>"NEW")+(P2:P500<>"OLD"))*((A2:A500="APPROVED")+(A2:A500="TEMPAPPROVED")))