Averageif with constraints

**NDnathan** · 2021-10-20, 11:18 AM

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.

**AliGW** · 2021-10-20, 12:51 PM

Probably, but to make it easier to help, please attach a small desensitised sample workbook.

**p45cal** · 2021-10-20, 09:41 PM

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")

**NDnathan** · 2021-10-21, 05:16 AM

Originally Posted by p45cal

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.

Thread: Averageif with constraints