+ Reply to Thread
Results 1 to 14 of 14

AVERAGEIF Multiple Columns

  1. #1
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    AVERAGEIF Multiple Columns

    Hi,


    I am trying to calculate an average of 5 months in 5 columns (D:H) if criteria in column A is met


    I have tried the following but doesn’t seem to be working


    =averageif(A:A,”Hire Purchase”,D:H)


    Any suggestions would be appreciated.


    Thanks in advance.

  2. #2
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: AVERAGEIF Multiple Columns

    Can you pls uload a sample file?
    Please make the Post as solved, when you get your answer & Click * if you like my suggestion

  3. #3
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: AVERAGEIF Multiple Columns

    Here is the sample.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: AVERAGEIF Multiple Columns

    Oh... I am not that much expert to solve this...
    Dont worry.. Others will help you

  5. #5
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: AVERAGEIF Multiple Columns

    Thanks for trying, anyway.

  6. #6
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: AVERAGEIF Multiple Columns

    Hi, As already you have average in column "J", you can simply use vlookup for this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    /Rgds,
    Ganesh Pitale

  7. #7
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Thumbs up Re: AVERAGEIF Multiple Columns

    Hi... got it..


    Sample (1).xlsx

  8. #8
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: AVERAGEIF Multiple Columns

    Quote Originally Posted by Ganesh7299 View Post
    Hi, As already you have average in column "J", you can simply use vlookup for this

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I was trying to avoid creating another column for the average and calculate it in the cell. Thanks for the advice anyway.

  9. #9
    Registered User
    Join Date
    11-22-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: AVERAGEIF Multiple Columns

    You're a star. I knew I could count on you here. As always, very helpful.


    Many thanks again.

    Quote Originally Posted by akhileshgs View Post
    Hi... got it..


    Attachment 245527

  10. #10
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: AVERAGEIF Multiple Columns

    Glad I could help you...

    Please change the topic to solved if you got what you needed

  11. #11
    Registered User
    Join Date
    06-16-2010
    Location
    India, Pune-411006
    MS-Off Ver
    Excel 2007/2010
    Posts
    89

    Re: AVERAGEIF Multiple Columns

    Hi,
    In addition to above example, it is average of "D4:H4", means in case of multiple occurrence it will not work.
    Below is one more solution for the same
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    04-25-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: AVERAGEIF Multiple Columns

    I am reviving an old thread because it is very similar to the question I have. Also, I don't think the question was answered all the way. The last poster, Ganesh7299, is correct. The solution provided in the reposted sample file returns the average only for the values in the row where the first occurrence of Hire Purchase happens. In the example, that is D4 through H4.

    What Ganesh7299 points out is that there is another row where Hire Purchase appears in Row 8. How do you get those values in cells D8 to H8 to also be part of the average? Ganesh7299 provides a solution, but when I paste it into the sample, I get a #NAME error. I thought there might be a few extra spaces or something in the formula, but that did not fix it. This is the revised version.

    =SUMPRODUCT((D4:H11)*(A4:A11=”Hire Purchase”))/(COUNTIF(A4:A11,”Hire Purchase”)*5)

    This is exactly what I want to know (use only some rows of data based on a criteria). I'm using Excel 2013 and Windows 7. Thanks.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,346

    Re: AVERAGEIF Multiple Columns

    fbrb99 Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  14. #14
    Registered User
    Join Date
    04-25-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: AVERAGEIF Multiple Columns

    Drat! Sorry I missed that. I will post a new one. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1