AVERAGEIFS with "AND" and "OR" criteria

Hi

I have an AVERAGEIFS formula with multiple criteria that I cannot get to work:

=IFERROR(AVERAGEIFS(Output!$R$26:$R$31876, Output!$U$26:$U$31876,1,Output!$D$26:$D$31876,"WIN",Output!$AL$26:$AL$31876,A10,Output!$AK$26:$AK$31876,"GOOD*",Output!$AK$26:$AK$31876,"DEAD",Output!$AK$26:$AK$31876,"DEAD4"),"No Data")

The "OR" criteria includes "GOOD*" (which includes multiple "GOOD" criteria) and then I need to also include "DEAD" and "DEAD4". I cannot use "DEAD*" as I need to include other versions of "DEAD" in a different formula.

Does anyone know how I can achieve this?

Hi,
try this

Formula:

=IFERROR(SUMPRODUCT(Output!$R$26:$R$31876* (Output!$U$26:$U$31876=1)*(Output!$D$26:$D$31876="WIN")*(Output!$AL$26:$AL$31876=A10)*OR((Output!$AK$26:$AK$31876="GOOD*"),(Output!$AK$26:$AK$31876="DEAD"),(Output!$AK$26:$AK$31876="DEAD4")))/SUMPRODUCT( (Output!$U$26:$U$31876=1)*(Output!$D$26:$D$31876="WIN")*(Output!$AL$26:$AL$31876=A10)*OR((Output!$AK$26:$AK$31876="GOOD*"),(Output!$AK$26:$AK$31876="DEAD"),(Output!$AK$26:$AK$31876="DEAD4"))),"No Data")

thank you

However I have a number of similar formulas across columns, could the formula above be written directly into VBA?

thank you that has worked. I am trying to write the formula in VBA but getting stuck here:

"=IFERROR(SUMPRODUCT(Output!$R$26:R$" & lastrow & "*(Output...

Can anyone help me here?

try something like this

dim i as long
dim sum as double, nr as long, average as double
with worksheets("Output")
for i=26 to 31876
if .range("U"&i) ="WIN" and .... then
sum=sum+.range("U"&i)
nr=nr+1
end if
end with
average=sum/nr
next i

ok here is sample file

the data will always change which is why I am using VBA to insert the formulas

put a sample file in attachament
you can resolve this without VBA or can write a UDF formula in VBA but I need the file

I will post in VBA forum to see if anyone can assist me there.

if you want to insert formula in cell try this

range("D25").Formula=range("D14").Formula

or you must insert double "" in formula
e.g.

Formula:

=A1&"XYZ"

range("A2").formula="=A1&""XYZ"""

thank you but I'm afraid I don't understand??

I'm not sure if I understand very well what you want.
Take a look in the attachment file and see if it does what you want.