I am not sure if this even can be done but I thought I would ask here since many have helped in the past.

I have an Excel sheet with HUNDREDS of tables (see attached example with only 3) that I need to perform calculations on. The tricky part comes in determining the number of values in each table. If it is a seven-point scale table, I need to find the sum of the top 3 values, the sum of the top 2 values, and the sum of the bottom three values. If it is a five point scale table, I need to find the sum of the top 2 values and the sum of the bottom two values. Any other table size is ignored.

Is there a way to count the number of cells in a range and based on that figure (6 which represents the 5-point scale with total, or 8 which represents the 7-point scale with total) and automatically insert the appropriate formulas for each range of tables?

As I said, the example is three tables, but I have worksheets that have 100, sometimes 200 tables. Right now I just set the formulas for a 7-point table and copy and paste the formulas one by one for every 7-point table and do the same thing for 5-point tables.

ExpertExchangeTopBottomExample.xlsx
here is the VBA code that will add my formula

into every table of the workbook. meaning on all tables in everysheets of the workbook.

it will create to columns at the end of each table, one will calculate bottom 2 and second one top 2

if you open attached file. see there are many sheets and many tables. simply run the macro by pressing Control SHIFT A and you will see what happens to the tables.

Open in new window

ExpertExchangeTopBottomExample.xlsm