Excel - Skipping a cell when calculating a total

April 2018


I have a series of codes back and forth it seems a bit insane.
but what I'm wanting is for cell b1 c1 d1 e1 and etc to add together which I have the following for this to return as my average

it looks like this >>>>

now the only problem I'm running into is if b1 has a number in it but c1:e1 does not have numbers

i want them to refer to another cell that dose have number while still viewing as blank

but excluding the blank cells within the final number

I'm working with %'s

any way its not working to well for me but this is the closest i have gotten to it working


but the final % is way off.. and I'm not sure if it is truly not counting the blank cells



You can use these formulas:

=SUM(B6:H6)/SUMPRODUCT((B$2:H$2) * (B6:H6<>"")) 



This finds the average based on the cells that are populated.


Thanks to rizvisa for this tip on the forum.
Published by aakai1056. Latest update on April 27, 2010 at 08:15 AM by aakai1056.
This document, titled "Excel - Skipping a cell when calculating a total," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - A macro to increase a cell reference
Excel - Conditional hide rows in multiple sheets