Mixing AVERAGEIF() with OR() & AND()

I'm having the issue where I need to mix an averageif, with multiple conditions, some OR and some AND.

I've started with:

=average(AVERAGEIFS(AI:AI,U:U,"Photo",D:D,"July 2013"),averageifs(AI:AI,U:U,"Photo",D:D,"Aug 2013")), but this doesn't average the full set together.

The logic I need to accomplish is, average range AI:AI IF U:U=Photo AND D:D="Aug 2013" OR D:D="Jul 2013"

Any ideas?

Thanks in advanced!
-G

Does column D contain real Excel dates or are they TEXT strings?

try this..

Formula:

Please Login or Register  to view this content.

if possible.. use D1:D100.. something like this.. in each whole column..

Hi,

You can use an array formula (ensure that you know how to enter these in Excel), though it's best practice not to reference whole rows/columns in these cases (I chose 1000 as an end reference - obviously amend to suit your needs):

=AVERAGE(IF((U2:U1000="Photo")*((D2:D1000="July 2013")+(D2:D1000="Aug 2013")),AI2:AI1000))

Regards

Try this array formula**.

Use cells to hold the criteria:

A2 = Photo
B2 = July 2013
C2 = Aug 2013


=AVERAGE(IF(U2:U100=A2,IF((D2:D100=B2)+(D2:D100=C2),AI2:AI100)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You should avoid using entire columns as range references with array formulas.

That's about it, yes.