# Thread: Averageif with constraints

1. ## Averageif with constraints

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.

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

3. 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")`

4. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•