Averageifs + "or", possible to do somehow?

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
I need to average cells which are labeled in a different column. Combining the averageifs function I think does not work with "or" criteria, so I tried this [but it's not working].

Is there a way to do this?

My effort:
=AVERAGE(IF(OR(DG2:DG1364=1212)+(DG2:DG1364=1222)),DJ2:DJ1364)

So I need to average column DJ, only for the rows whose DG column values equal 1212 or 1222. I need to use "or" not "and".

Thank you...!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,303
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(ISNUMBER(MATCH(DG2:DG1364,{1212,1222},0)),DJ2:DJ1364))
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I need to average cells which are labeled in a different column. Combining the averageifs function I think does not work with "or" criteria, so I tried this [but it's not working].

Is there a way to do this?

My effort:
=AVERAGE(IF(OR(DG2:DG1364=1212)+(DG2:DG1364=1222)),DJ2:DJ1364)

So I need to average column DJ, only for the rows whose DG column values equal 1212 or 1222. I need to use "or" not "and".

Thank you...!
You were pretty close. Just remove the OR:

Array entered**:

=AVERAGE(IF((DG2:DG1364=1212)+(DG2:DG1364=1222),DJ2:DJ1364))

** 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.
 

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
Awesome, thank you Biff !! :) I really appreciate your help, thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,196
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top