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.

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

If so then you could use

Formula:

Please Login or Register  to view this content.

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

BSB

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")

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

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!

This works

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

The cells are 0 but you formatted them to hide zeros

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

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