AVERAGEIFS and excluding a term.

Hi All!

I have an formula issue that I can't seem to figure. Using the data below as an example how can I figure out the average of 'Range 2', if the world 'sun' is included in 'Range 3' and is not 'W' in 'Range 1'


Also to note, I have to exclude 'W' in Range 3 as the real data set has a lot more than 4 choices in 'Range 3'

I've been stuck on this forever and messing around with AVERAGEIFS but cannot get it to exclude all 'W' results in Range 1. so if anyone knows you would make my week!

Screen Shot 2015-11-17 at 9.31.05 PM.png

Thanks!

See if this syntax works.

=AVERAGEIFS(range2,range1,"<>"&"W",range3,"*"&"sun"&"*",range3,"<>"&"W")

the range3 <> W part is probably unnecessary. Range3 is already limited to finding anything with "sun" in it.

If this doesn't work try uploading a workbook file.

'CRIT'!B2 and 'Properties'!F4 look like cell reference so

=AVERAGEIFS('Main Report'!D:D,'Main Report'!A:A,"<>"&'CRIT'!B2,'Main Report'!H:H,"*"&'Properties'!F4&"*")

might works.

beebo021999 spotted it. It was the quotes around the sheet names and reference cells. Those quotes are necessary for literals. Your new formula is now passing those strings by reference.

You are welcome. Thank you for the feedback.