AVERAGE using multiple criteria

[Julz]

Hopefully this will be my last question for a while.

If column Z=23902 or 28971, Average column G.

=AVERAGE((IPT_opened_ticket_report.xls!$G$2:$G$20000)*((IPT_opened_tick
et_report.xls!$Z$2:$Z$20000=29302)+(IPT_opened_ticket_report.xls!$Z$2:Z
$20000=28751)))

Thanx!!
~Julz

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER)
=AVERAGE(IF((IPT_opened_ticket_report.xls!$Z$2:$Z$20000=29302)+(IPT_ope
ned_ticket_report.xls!$Z$2:Z$20000=28751),IPT_opened_ticket_report.xls!
$G$2:$G$20000))

 

[Julz]

I left something out on my earlier request.
If column N=1 and If column Z=23902 or 28971, Average column G.

thanx for your help!!

 

[Frank Kabel]

Hi
not tested but try
=AVERAGE(IF(((IPT_opened_ticket_report.xls!$Z$2:$Z$20000=29302)+(IPT_op
e
ned_ticket_report.xls!$Z$2:Z$20000=28751))*(IPT_opened_ticket_report.xl
s!$N$2:$N$20000=1),IPT_opened_ticket_report.xls!
$G$2:$G$20000))

 

[Julz]

Frank, I couldn't get it to work with or without array. Any other
suggestions?

 

[Frank Kabel]

Hi
try the following non-array solution
=SUMPRODUCT((IPT_opened_ticket_report.xls!$N$2:$N$20000=1)*((IPT_opened
_ticket_report.xls!$Z$2:$Z$20000=23902)+(IPT_opened_ticket_report.xls!$
Z$2:$Z$20000=28751)),IPT_opened_ticket_report.xls!$G$2:$G$20000)/SUMPRO
DUCT((IPT_opened_ticket_report.xls!$N$2:$N$20000=1)*((IPT_opened_ticket
_report.xls!$Z$2:$Z$20000=23902)+(IPT_opened_ticket_report.xls!$Z$2:$Z$
20000=28751)))

 

[Julz]

I appreciate your continued expertise, however, I'm now getting a #DIV/0!
error.
Any idea what the cause may be?

 

[Frank Kabel]

Hi
looks like no data matches your criteria
If you like, email me your file and I'll take a look at it

 

[Julz]

Files are on the way! They are coming from email address-
(e-mail address removed)

thanx!