COUNTA: Calculating Dynamically Changing Rows

[gsof]

Hi All,


I am a bit of a newbie to Excel's formulas so apologies in advance for incorrect use of terminology.


However I have an issue that I was wondering if anyone could help me with.


I have a report that varies in row size daily, and I need to count how many employees we have in that one day. So for example one day we may have 50 employees whereas the next day we may only have 4 employees (please see the example I have created).

Employee Number	Name	Age	Favourite Colour
gs111 <tbody> </tbody>	Gary Smith	10	Red
as222 <tbody> </tbody>	Alan Smith	56	Blue
bc333 <tbody> </tbody>	Barry Corr	23	Red

<tbody>
</tbody>

Currently I am using COUNTA and manually changing this. However I was wondering whether there was a way for the formula to automatically do this so I do not have to keep manually editing the formula daily.


Many thanks in advance.


gsof

 

[JackDanIce]

Easy answer might be:

=counta(B:B)

 

[gsof]

Thanks for the reply JackDanIce

however I am starting the counta from A9 (A8 including the table header), so if I do =counta(A9:A) it only selects cell A9

 

[Darren Bartrup]

Edit: Deleted as same reply already given.

 

[JackDanIce]

This may have your answer:
Dynamic Named Ranges That Expand and Contract Automatically in Your Excel Spreadsheet

 

[Aladin Akyurek]

Thanks for the reply JackDanIce

however I am starting the counta from A9 (A8 including the table header), so if I do =counta(A9:A) it only selects cell A9

Define EMPLOYEES by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=Sheet1!$A$9:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Adjust the sheet name to suit.

Usage:

=COUNTIF(EMPLOYEES,"?*")

would return an employee count.

 

[gsof]

Thanks for your reply again JackDanIce, however I believe I have to pay for this add-in unfortunately thats something I can't do.

Again though I do appreciate the help you've offered

 

[JackDanIce]

Aladin's suggestion will work and no, you don't have to pay for the add-in, it's different ways of defining a dynamic (changing) range and how to apply formulae to it; more for your benefit only!

 

[Aladin Akyurek]

Thanks for your reply again JackDanIce, however I believe I have to pay for this add-in unfortunately thats something I can't do.

Again though I do appreciate the help you've offered

In case you are going to miss it or already missed: See Post #6.

 

[gsof]

Thanks Aladin Akyurek,

Apologies I have tried your example and it is not returning back the number of employees in my practice table :\
in using =COUNTIF(EMPLOYEES,"?*") it is returning back #VALUE!