Calculated Column Error in Tables

[bobbyscar]

I am having a huge amount of trouble with calculated columns, and errors with them when adding new rows to a table. I am trying to maintain a list of sales while dynamically maintaining a list of unique customers who make purchases. I am using a helper column that counts new customers are they are added to the transaction table. However, when I add rows to the table, my helper column's formula is changing on its own. Please help!

Here is a link to a worksheet with an example (source: https://people.highline.edu/mgirvin/ExcelIsFun.htm):
https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT815-818.xlsx

Repro steps (should take 1 minute to reproduce):

1. Open worksheet
2. Enable Editing
3. Go to tab "(817&818) (an)"
4. Observe formula in O405
   1. =SUM(IF(COUNTIF(B$2:B405,B405)<>1,0,COUNTIF(B$2:B405,B405)),O404)
5. Create a new row in this table by entering content into A406
6. O406 reflects the correct formula
   1. =SUM(IF(COUNTIF(B$2:B406,B406)<>1,0,COUNTIF(B$2:B406,B406)),O405)
7. Create another new row in this table by entering content into A407
8. O406 formula has changed to
   1. =SUM(IF(COUNTIF(B$2:B407,B406)<>1,0,COUNTIF(B$2:B407,B406)),O405)

 

[circledchicken]

Hi and welcome to the forum,

I'm not sure if this is a known issue, but the behaviour you have identified does seem 'buggy' to me. A couple of other strange behaviours include:

• after step 7 in your steps above, immediately undo changes 7 and 5. Now redo step 5. This time the incorrect version of the formula appears the first time (it is no longer as shown in step 6).
• delete all the formulae in the Helper column and re-enter the formula in in the first data cell of the column, letting it propagate down the column as normal. Now add a new row as per step 5. Again step 6 in your list now doesn't happen, it shows the incorrect formula.

Here are some possible workarounds:

Step 1: Shorten your existing formula

To make it easier to follow, your existing helper column formula:

=SUM(IF(COUNTIF(B$2:B2,B2)<>1,0,COUNTIF(B$2:B2,B2)),O1)

can be reduced to this which does the same thing:

=SUM(IF(COUNTIF(B$2:B2,B2)=1,1,0),O1)

which can further be reduced to this (since the logical value TRUE is treated as 1 and FALSE as 0):

=SUM(COUNTIF(B$2:B2,B2)=1,O1)

Replacing your existing formula with this last version still retains the 'buggy' behaviour on row addition.

Step 2: Use structured references

When working with tables it is usually best to use structured references. Amending the column formulae to this semi-structured reference form should fix the issue:

=SUM(COUNTIF(B$2:[@Country],[@Country])=1,O1)

You could then go one step further and use a fully structured reference form like this:

=SUM(COUNTIF(INDEX([Country],1):[@Country],[@Country])=1,
     INDEX(Table24[[#All], [Helper]],ROWS(INDEX([Helper],1):[@Helper])))

I'm not sure this last version helps with readability and maintenance though, although there might be better ways of writing it.

It seems like it might be much easier to do what you want using a PivotTable. Using the table as a data source means the range is dynamic - you would just need to refresh the table. If you wanted to automate the refresh, a little bit of code would allow you to do this, see:

• VBA Express : Excel - Auto Refresh Pivot Table on Activate Worksheet
• Refresh Pivot Table via VBA. Update Pivot Table With VBA

Note:

• To learn more about the use of structured references see: Using structured references with Excel tables - Excel - Office.com
• Another, unrelated table bug is reported here: Table Extension recalculation bug in Excel 2010 but not 2007 « Excel and UDF Performance Stuff
• A query or parametrised query may also be of interest to you, see here for a detailed tutorial: Build Queries
• For a non-dynamic solution, advanced filter might suffice: Filter for unique values or remove duplicate values - Excel - Office.com

 

[bobbyscar]

Wow, thanks very much. Great reply, and thanks for all of the follow up resources.

 

[circledchicken]

You're welcome! Thanks for the feedback.