Help with counting formula

[Lisa SW]

I am keeping a spread sheet of our customer base and what medium they learned
about our company from our website to an advertising postcard mailing and
have labeled a column for each after the general name and address
information. I put the number 1 in the appropriate column for counting
purposes. I have taken label of each medium and in the last column created a
tally box for quick reference. I put the formula of =SUM(A2:A17) which works
wonderful. But as I add to the list I am going past 17 and the tally for
those are not counted. Does anyone have a formula that will incorporate the
additional rows into the tally? Also is there a formula that will show the
number of rows so I can show with the tally's an "out of" number?

Thank you kindly for any help.

 

[Domenic]

If you're using Excel 2003, convert your data into a list...

Data > List > Create List

The range will automatically adjust as data is added/removed. Otherwise
use a dynamic named range....

Insert > Name > Define

Name: Range

Refers to:

=$A$2:INDEX($A$2:$A$65536,MATCH(9.99999999999999E+307,$A$2:$A$65536))

Click Ok

Then try...

=SUM(Range)

and

=COUNT(Range)

Hope this helps!

http://www.xl-central.com

 

[Legare]

I assume that you are adding to the list by putting new entries at the end,
in the case of your example A18. If A18 does not include any data, then you
can include it in the formula =SUM(A2:A17) so that it looks like
=SUM(A2:A18). That will not affect the SUM since the SUM function ignores
cells that do not contail numeric values. Now, when you need to add data,
insert a new row in front of row 18 and Excel will automatically update the
formula to =SUM(A2:A19). You can now add the data to row 18 and it will be
included in the SUM. I would also put some text in the current row 18 (like
several equal signs in each cell) that would remind me to insert the empty
row before adding data. This text row will shift down each time you insert
new data rows to remind you where to insert the next new data.

 

[Lisa SW]

Thank you, this will work fine.
--
Lisa SW

I assume that you are adding to the list by putting new entries at the end,
in the case of your example A18. If A18 does not include any data, then you
can include it in the formula =SUM(A2:A17) so that it looks like
=SUM(A2:A18). That will not affect the SUM since the SUM function ignores
cells that do not contail numeric values. Now, when you need to add data,
insert a new row in front of row 18 and Excel will automatically update the
formula to =SUM(A2:A19). You can now add the data to row 18 and it will be
included in the SUM. I would also put some text in the current row 18 (like
several equal signs in each cell) that would remind me to insert the empty
row before adding data. This text row will shift down each time you insert
new data rows to remind you where to insert the next new data.