Averageifs

My formula will work when I'm averaging one person but not two. I'm need to get an average with 3 criterias. In column N I have the names and in column P I have the # of sales. I need the average the two people when they have sales that are >30.

My Formula
=AVERAGEIFS(P:P,N:N,R9,N:N,R10,P:P,R11)

Index
R9=Name 1
R10=Name 2
R11=>30

This didnt work. Its giving me the #DIV/0 error which is what I'm getting with my formula aswell. Is it not possible to use Averageifs when theres more than one criteria in one column?

I would suggest a helper column, say column S for example
S1: =OR(N1=R$9,N1=R$10) and fill down

Then use
=AVERAGEIFS(P:P,S:S,TRUE,P:P,R11)

Originally Posted by yuenk

Is it not possible to use Averageifs when theres more than one criteria in one column?

Depends on what those criteria are.
The criteria in Averageifs (and countifs and sumifs) are AND criteria. They must both be true.
If it's possible for BOTH criteria to be true for any single cell, then Yes.
Example, >5 AND <10, it is possible for a single cell to be both Greater than 5 AND less than 10 (6 7 8 and 9)

If it's NOT possible for BOTH criteria to be true for any single cell, then No.
Example, "x" and "y", it's not possible for a single cell to be BOTH "x" AND "y"..

Thanks I'll use this method.

Just curious. I understand what youre saying that a single cell cant contain X and Y. If the column contains X and Y but not in the same cell why didnt my formula work?

Because it checks every single cell one at a time, and evaluates BOTH the criteria for EACH single Cell.
And only counts each one where BOTH criteria are TRUE.

Maybe this will help clear it up.
Take your original attempt
=AVERAGEIFS(P:P,N:N,R9,N:N,R10,P:P,R11)

This is all assuming R9 and R10 are NOT the same value (if they are the same, then what's the point..)

P1 will be included in the average IF: N1=R9 AND N1=R10 AND P1=R11 (N1 cannot be equal to BOTH R9 AND R10)
P2 will be included in the average IF: N2=R9 AND N2=R10 AND P2=R11 (N2 cannot be equal to BOTH R9 AND R10)
P3 will be included in the average IF: N3=R9 AND N3=R11 AND P3=R11 (N3 cannot be equal to BOTH R9 AND R10)

I understand. I looked at it as if any of those statements were true it would calculate it regardless if one of the three were false. But now that you explained it further I see why my formula doesnt work.

You're welcome.

ha Thanks Jonmo