# Excel - Skipping a cell when calculating a total

## Issue

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 >>>>
` =SUM(AVERAGE(B11:H11))/SUM(AVERAGE(\$B\$5:\$H\$5)) `

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

`=SUM(IF(B9="",B9,\$B\$5),IF(C9="",C9,\$C\$5),IF(D9="",D9,\$D\$5),IF(E9="",E9,\$E\$5),IF(F9="",F9,\$F\$5),IF(G9="",G9,\$G\$5),IF(H9="",H9,\$H\$5)/SUM(\$B\$5:\$H\$5))`

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

ANY HELP WOULD BE GREATLY APPRECIATED

## Solution

You can use these formulas:

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

or

`=SUM(B6:H6)/SUMIF(B6:H6,"<>",B\$2:H\$2) `

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

## Note

Thanks to rizvisa for this tip on the forum.