COUNTIF FUNCTION DOESN'T WORK IN TABLE FORMAT WITH ENTRY FORM

[Dennis1951]

I am working on a spreadsheet to build a simple billing system for my son's business.  He is not at all proficient with Excel.  I am wanting to use the COUNTIF function to count the running occurrences of a text entry in a column.  i.e.  The first time it comes up it will return a 1, the second a 2, the third a three and so on.  It works well in a spreadsheet.  However, when I go to the table format and use the entry form, when I input a new row, it changes the rows above and puts the total count in those rows.  

Just for discussion suppose I have a spreadsheet with two columns A and B.  A will be text and B will be my function:   =COUNTIF($A$1:A1, A1)

It works fine in the spreadsheet form.  Let's make it simple.  Here is the result in a small sheet.

Column A      Column B

AAA                     1

BBB                      1

CCC                     1

DDD                    1

AAA                     2

If I continue to put in AAA and drag down the function it will count up, 3, 4 so forth.

But if I go to a table and then use an entry form and put in AAA, here is what I will get.  

Column A      Column B

AAA                     1

BBB                      1

CCC                     1

DDD                    1

AAA                     3    <-------------------

AAA                     3

In the spreadsheet this would stay =COUNTIF($A$1:A5, A5) and give a 2.  But in the table format when I have entered the next AAA it will become =COUNTIF($A$1:A6, A6) and be a 3 as I show here.  Entering more AAA entries will change the bottom two to 4 and then to 5 and so forth.

I spent 4 hours on a chat with 4 different people with microsoft and they gave up and said that I should just drag down the function every time.  That just negates any reason to have an entry form.

Can anyone help?  Thanks.

Frustrated in Illinois,

Dennis1951

[#2]

@Dennis1951 

I believe that is a feature, and not an error. One of the main benefits of using tables is formulas that reference a table column will auto-update when the table is resized.

If, in another cell on your worksheet, you had =SUM(B2:B7) and then resized your table to add another row, then your sum formula would update to =SUM(B2:B8), which is a desirable feature. In your case, the last row of your table is referencing an entire column of the table and is getting updated as well (it doesn't appear to discriminate between updating formulas outside the table and within the table).

I would suggest using structured table references instead of A1 notation:

=COUNTIF(INDEX([Column A],1):[@[Column A]],[@[Column A]])

[#3]

@Dennis1951 

You need to provide the static reference from the table's first record in order for the function you wrote to work with a table, as seen in the example below.

The first record in the table is in row 2, hence row 2 will be fixed in the first reference, which is $A$2 in the table below. The rest of the cell references will be dynamic and will not include the $ symbol. Copy this formula then to the entire column.

I hope it resolve your question