offset formula question

ariel20029

Board Regular
Joined
Jun 20, 2013
Messages
97
Hi,
I have a data set where the number of rows change when the data is refreshed. I am trying to count the number of rows in the population ( not including blanks)..

I am tired of manually having to update the last cell of the range in the formula every time I refresh. I have dozens of spreadsheets where I have to update formula to get the population.

Data starts on column E25


Column E
5%
8%
(blank)
7%

Right now my formula is =offset(E25:E5000)-1,0)
I entered as an array ( not sure if it needs to be or not)

the minus one is to subtract the total. For some reason I am getting an answer with the count of 1.. can you please help.. I tried an count A formula and that did not work either.


thanks,
Sharon
 

Some videos you may like

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.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,775
If you just want to count the number of cells with numbers in them:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Header</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">8%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">7%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=COUNT(<font color="Blue">E:E</font>)</td></tr></tbody></table></td></tr></table><br />

Subtract 1 if there's a total line, but I don't see why you'd want to total up percentages.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,545
Messages
5,469,346
Members
406,647
Latest member
ssinovec

This Week's Hot Topics

Top