AVERAGEIFS with "AND" and "OR" criteria

[maym]

Hi

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

Please Login or Register  to view this content.

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?

[TudyBTH]

Hi,
try this

Please Login or Register  to view this content.

[maym]

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

Please Login or Register  to view this content.

Can anyone help me here?

[TudyBTH]

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

[maym]

thank you

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

[TudyBTH]

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

[maym]

ok here is sample file

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

[maym]

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

[TudyBTH]

if you want to insert formula in cell try this

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

[maym]

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

[TudyBTH]

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.