+ Reply to Thread
Results 1 to 7 of 7

Help please excel 2003 formula- averaging with criteria

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    2

    Help please excel 2003 formula- averaging with criteria

    Hi

    I have a fairly simple spreadsheet that i need a little help with.

    column J has either north or south written in. Column K has numbers.

    1. I need to sum all non blanks of column K if column J equals north/south.
    2. I need to average the non blanks of column K that only are against column J north/south.

    Many thanks for your help!
    Last edited by paulsmyl; 07-02-2013 at 08:31 AM. Reason: change title

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Help please excel 2003 formula

    1. For Sum use below
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    2. For Average use below..
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In case 2007+ Excel verison, you can use SUMIFS or AVERAGEIFS also
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Registered User
    Join Date
    07-02-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Help please excel 2003 formula

    Hi Debraj

    Thanks.

    The sumif formula works fine but the averageif formula gives me an error of 'unrecognized text'

  4. #4
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Help please excel 2003 formula- averaging with criteria

    OOPS.. I have stop using 2003 Excel version..

    you can try this CSE formula..

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


    Please confirm the formula by pressing Ctrl + Shift + enter Not just enter..

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Help please excel 2003 formula- averaging with criteria

    You actullay need to wrap the SUMIF in SUM() to sum all non-blanks that include either 'North' or 'South'

    hence
    for SUM
    =SUM(SUMIF(J2:J11,{"North","South"},K2:K11))

    for AVERAGE
    =SUM(SUMIF(J2:J11,{"North","South"},K2:K11))/SUM(COUNTIF(J2:J11,{"North","South"}))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  6. #6
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Help please excel 2003 formula- averaging with criteria

    Thanks Ace_XL.. How did I missed this..

  7. #7
    Registered User
    Join Date
    04-14-2009
    Location
    The World
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Help please excel 2003 formula- averaging with criteria

    anoth way my help
    to Sum
    Please Login or Register  to view this content.
    for average with CSE formula
    Please Login or Register  to view this content.
    CSE must enter y Ctrl+Shift+Enter

+ 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