Welcome to the community, @Andrew_Pyle! Unfortunately you can’t do a straight copy of a working formula from a spreadsheet and expect it to work in Airtable. Airtable is designed more like a database than a spreadsheet, and many of the elements in that formula don’t exist in Airtable’s formula syntax:
Airtable formulas also don’t support arbitrary row/cell references (e.g. B4), or references to data in other tables (e.g. Widgets!B2:B). Also, because an Airtable formula field may only contain a formula, there’s no need to start it with an equals symbol.
I suggest spending some time reviewing other formula discussions here in the forum, and also reading through the formula field reference to understand what functions are available:
What you’re trying to achieve may be possible through a combination of design changes—you’ll need to add link fields to access data from other tables using rollup/lookup fields—and formula refactoring, but you’ll need to spend some time learning the key differences between spreadsheets and Airtable before we can dig into the nitty-gritty of what you want to accomplish.