Results 1 to 4 of 4

Thread: Averageif with constraints

  1. #1
    Neophyte NDnathan's Avatar
    Join Date
    Oct 2021
    Posts
    2
    Articles
    0
    Excel Version
    2017

    Averageif with constraints



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Probably, but to make it easier to help, please attach a small desensitised sample workbook.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,028
    Articles
    0
    Excel Version
    365
    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. #4
    Neophyte NDnathan's Avatar
    Join Date
    Oct 2021
    Posts
    2
    Articles
    0
    Excel Version
    2017
    Quote Originally Posted by p45cal View Post
    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
  •