You don't need an automation for that.

In your "Sales" table, you need to create a linked record field called "Category". That field needs to be linked to a brand new table that you create called "Categories".

Then, in your "Categories" table, Airtable will automatically create a new linked record field for you there that links back to your "Sales" table. Leave that field alone.

Next, in your "Categories" table, you would create a Rollup field based on the linked record field, and choose your "Total" field from the "Sales" Table. For the rollup formula, type in: SUM(values)

Then, you will have the Total for each category.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Does this look right?

Link to base

If so, you'll just need to create a link between your 'Sales' table and your 'Totals by Category' table, and then create a rollup field in 'Totals by Category' to sum the totals

To populate all your existing data, click on the field header for the existing 'Categories' field in 'Sales' so that the entire column is selected then hit CMD/CTRL + C.  Then, hit the field header for the linked field to the 'Totals by Category' table, then paste in the data.  This'll link everything up for you and if you go to 'Totals by Category' you should see all the data you want!

Thank you for repeating the solution that I outlined above.

Also, he wouldn't need to copy and paste the old category field to the new category linked record field. For a quicker approach, he can just convert the existing category field to a linked record field, which will automatically create the new table for him.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld