Results 1 to 3 of 3

Omit blank cells from SUMPRODUCT average formula?

Threaded View

  1. #1
    Registered User
    Join Date
    09-03-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Omit blank cells from SUMPRODUCT average formula?

    I'm using the following formula to get an average of specific percentages matching specific cells in a specific column

    =SUMPRODUCT(($B$2:$B$169="Extra")*((AA$2:AA$169)/COUNTIF($B$2:$B$169,"=Extra")))

    i.e under each monthly column this formula displays the average for all 'Extra' services that month.

    Column A = List of sites
    Column B = Standard, Premium, Extra
    Columns C to X = Percentages for that month


    Site : Service : May 09 : June 09 : July 09
    SITE A : Standard : 100% : 90.99% : 95.34%
    SITE A : Premium : 100% : [Blank] : 95.34%
    SITE A : Extra : 95.55% : 90.99% : 95.34%
    SITE B : Standard : 100% : 90.99% : [Blank]
    SITE B : Premium : 100% : 90.99% : 95.34%
    SITE B : Extra : 95.55% : [Blank] : 93.34%
    SITE C : Standard : 100% : 90.99% : 95.34%
    SITE C : Premium : [Blank] : 90.99% : 95.24%
    SITE C : Extra : 95.55% : 90.99% : 95.34%

    The formula is working perfectly except when the percentage column has a blank cell, which returns an incorrect result.

    Is there a way to adapt this formula so that blank cells are ignored data range of percentages.

    Please note I don't want to change the order of the rows in the table.

    Thanks in advance!
    Last edited by Radchek; 09-03-2010 at 08:14 AM.

Thread Information

Users Browsing this Thread

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

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