# Count while Skipping Blank Then find Percentage based on total numbers in cell

#### Excelnoub

##### Board Regular
Good evening everyone,
When it comes to Formulas I am so lost. I am looking for the following formula.

I am working with the following Sum formulas
=SUM(E3:E20)/7

I have information to write in E3 to E20. (Numbers) some cells will be blank.

I need the following formula:

Look in E3 to E20 and calculate the total Then
Calculate the total amount of cells populated then keep it in bank
Now take the first total then divided it by the total amount of cells (populated) while skipping the blank cells

Example:

E3: 2
E4: 4
E5:
E6: 7
E7: 2

Total: 15
There is 4 numbers from E3 to E7 therefore divide this total number by 4 = 3.75

3.75 Should go in E9.

How in the H can I do this...

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
=sum(e3:e7)/max(1,count(e3:e7))

=iferror(averageif(e3:e7),0)

How do I write this to one specific cell Ie: E9 cell =sum(e3:e7)/max(1,count(e3:e7)) =sum(e3:e7)/max(1,count(e3:e7))?

Sorry for this... Good with VB but formula I suck

How do I write this to one specific cell Ie: E9 cell =sum(e3:e7)/max(1,count(e3:e7)) =sum(e3:e7)/max(1,count(e3:e7))?

Sorry for this... Good with VB but formula I suck

I don't understand... Just type the formula in E9 as given.

Got it thank you,

There was also the following: =Sum(e3:e7)/Count(e3:e7)

Got it thank you,

There was also the following: =Sum(e3:e7)/Count(e3:e7)

What I meant is to enter in e9...

Either:

=sum(e3:e7)/max(1,count(e3:e7))

Or

=iferror(averageif(e3:e7),0)

Or, if you want to, just

=sum(e3:e7)/count(e3:e7)

The latter will yield #DIV/0! when there is nothing in e3:e7.

Replies
2
Views
207
Replies
0
Views
171
Replies
2
Views
472
Replies
7
Views
235
Replies
3
Views
80

1,196,329
Messages
6,014,678
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back