Excel - Skipping a cell when calculating a total

December 2016


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.

Related :

This document entitled « Excel - Skipping a cell when calculating a total » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.