Averageif with constraints

NDnathan

New member
Joined
Oct 20, 2021
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2017
Hello! I have a formula as follows

Code:
=AVERAGEIFS(Sheet2!AE:AE,  Sheet2!A:A,A1, Sheet2!AE:AE,">0")
But I only want to average those numbers when there are 5 or more such instances of positive numbers.

Tried this but it doesn't work:

Code:
=AVERAGEIFS(Sheet2!AE:AE,  Sheet2!A:A,A1,  Sheet2!AE:AE,">0",  Sheet2!AE:AE,(COUNTIF(Sheet2!AE:AE,">0"))>=5)
I'm guessing this has a simple solution, but I've been thinking about it for awhile and can't figure it out.
 
Probably, but to make it easier to help, please attach a small desensitised sample workbook.
 
try:
Code:
=IF(COUNTIFS(Sheet2!A:A,Sheet1!A1,Sheet2!AE:AE,">0")>=5,AVERAGEIFS(Sheet2!AE:AE,  Sheet2!A:A,A1, Sheet2!AE:AE,">0"),"fewer than 5")
 
try:
Code:
=IF(COUNTIFS(Sheet2!A:A,Sheet1!A1,Sheet2!AE:AE,">0")>=5,AVERAGEIFS(Sheet2!AE:AE,  Sheet2!A:A,A1, Sheet2!AE:AE,">0"),"fewer than 5")

This works like a charm! I truly appreciate the help.
 
Back
Top