+ Reply to Thread
Results 1 to 9 of 9

SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

  1. #1
    Registered User
    Join Date
    01-15-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    3

    SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    Hello,

    Running into a problem. Here's the situation:

    Cell range B10-M10, sum the cells 3,5,7,9 from it's respective column into a total
    Example: Cell B10: =SUM(B3,B5,B7,B9)
    Same formula for each column in the 10th row.
    So, cell N10 should be the SUM of B10:M10 divided by the number of cells WITH data (don't include blank cells) as the numbers will be calculated when entered in on a weekly basis.

    Here's what I've been using for N10: =SUM(B10:M10)/COUNTA(B10:M10) problem is it doesn't work, it still calculates the empty cells. When I used the formula on cell data NOT obtained from another function (just testing in blank worksheet) it worked fine. I wouldn't imagine that the means of how a cell's data is calculated would have any impact on another function working, but that seemingly is the case here.

    In the screenshot, you will see only cells B10-E10 are filled out. It should be dividing the current sum of those (28) by the number of cells with data (4). N10 should therefore be "7", not "2.33"
    001.jpg

    Any guidance would be great appreciated! Thank you.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    Have you tried the AVERAGEIFS function? If you can't get it to work, attach the workbook, not a picture of it. The thing is that the cells are not truly blank because they contain a formula. You could try this:

    =SUM(B10:M10)/(COUNTA(B10:M10)-COUNTBLANK(B10:M10))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    Are cells F9:M9 truly blank? i.e. no formula in them?

    If so then you could use
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If not then even adding COUNTBLANK to the formula wouldn't work.

    BSB

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    In column F onward are the values 0 BUT "hidden" as =SUM(B3,B5,B7,B9) will yield 0 for empty cells

    If so,

    =AVERAGEIF(B10:M10,">0")

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    Why not just
    =AVERAGE(B10:M10)
    Text and Blanks are already ignored by design.

  6. #6
    Registered User
    Join Date
    01-15-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    3

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    Hello and thank you for your replies.

    I've tried the suggestions, but nothing worked. I guess it may because in reference to BadlySpelledBuoy's question, are they truly blank, the answer would be no.

    For example the B column, the B3,B5,B7,B9 cells each have a formula within that generates a value based upon the cell above. B3's data is based upon B2, B5's on B4, etc. This is the same for columns B-M.

    I apologize, I never really considered that a formula would count as being not blank. I was just considering it blank since there was no value.

    AliGW suggested that I attach the workbook, so I have. Thank you again for everyone's help!
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    This works

    =AVERAGEIF(B10:M10,">0")

    The cells are 0 but you formatted them to hide zeros

  8. #8
    Registered User
    Join Date
    01-15-2018
    Location
    Buffalo, New York
    MS-Off Ver
    2016
    Posts
    3

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    That did it John! Thank you again everyone for your help, much appreciated!!!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/COUNTA Function Not Working On Cell Data Generated from SUM Function

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 06-02-2015, 01:38 AM
  2. Counta function
    By Tracyd2475 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2015, 09:29 AM
  3. [SOLVED] COUNTA function to count from first cell to date in column header
    By mr_mango81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2015, 01:03 AM
  4. COUNTA Function in VBA
    By mccreaso in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2013, 08:24 PM
  5. [SOLVED] Using COUNTA Function
    By ExcelNovice79 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2012, 03:37 PM
  6. Function CountA not working... Why????
    By Frustrated1 in forum Excel General
    Replies: 2
    Last Post: 02-17-2010, 11:49 AM
  7. COUNTA function
    By Rafis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-02-2005, 11:05 AM

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