AverageIf Formula with 2 criteria

[lostinaformula]

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!

[benishiryo]

welcome to the forum. you are very close, i think. and the reason why i can only guess is because we cannot see what is in your file. do upload an excel sample so that we do not have to manually key in your data to do a testing.

input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

if i really guessed it wrongly, please upload something according to my recommendations. try to use the solution i have given and we'll help to advise what went wrong.

my guess is:
=AVERAGEIFS(Mission!$C$1:$C$122,Mission!$A$1:$A$122,"="&C4,Mission!$C$1:$C$122,"<300")
you are missing the red portion. you need to tell Excel what to average when A1:A122=C4 and C1:C122 is lesser than 300. i have uploaded a sample file where it will average the yellow cells in Mission worksheet (A1:A122=C4 and C1:C122 is lesser than 300).

[FDibbins]

AVERAGEIFS only works on open workbooks, so if the other file is closed, it will return an error.

AVERAGEIFs() also starts with the average-range, then lists the criteria range and criteria (unlike the regular AVERAGEIF) - looks like you have it misplaced

Also, you dont need to include "=" if it is a direct reference like that (its not wrong, just unnecessary)

=AVERAGEIFS(Mission!$A$1:$A$122,"="&C4,Mission!$C$1:$C$122,"<300")
you could just use...
=AVERAGEIFS(Mission!$A$1:$A$122,C4,Mission!$C$1:$C$122,"<300")

[lostinaformula]

Thank you!!!! That did it . Saved me a lot of time experimenting...

Is there a really good book/course/resource for these (seemingly more advanced) functions/formulas?