Hi all,

I'm working on a project due tomorrow (or technically today) and I hit a roadblock with the averageif formula I need to calculate.

I'm trying to combine 2 criteria using an external reference.

The first criteria is that the a range in an external worksheet matches a line in the current worksheet (i.e. that's the external reference: i.e. AVERAGEIFS(Mission!$A$1:$A$122,"="&C4,)

I then want to take the averages of a different column in the external worksheet for only the values that are less than 300 and return that i.e.: Mission!$C$1:$C$122,"<300") OR Mission!$C$1:$C$122"<300"),Mission!$C$1:$C$122 .


I have this so far (that's not working):

=AVERAGEIFS(Mission!$A$1:$A$122,"="&C4,Mission!$C$1:$C$122,"<300")

I also tried: =AVERAGE(IF((Mission!$A$1:$A$122,"="&C4)+(Mission!$C$1:$C$122"<300"),Mission!$C$1:$C$122))

Any suggestions? Thanks in advance!