Hello, thought I'd share this really easy SUMIFS FORMULA equivalent of PIVOT TABLE.
Just needs UNIQUE extra spill + INDEX of each Category Column! SEE 3RD TAB attached TABLE 1.
The reason I've shared this! is that Statisticians, Accountants, Mathmaticians will just use Pivot Tables, and so many people who want a Formula version, that works easily, I'm putting it out there! As many people who can, it will be a clandestine secret, such as... oh its easy, index, you'll figure out it, the answer is out there, was it wasn't, itis now...
Well here its is attached, as pictured.
@stephanrs
Not sure if this forum is the correct place to express ideas like yours. I believe the purpose here is primarily to ask questions and get help. Having said that, I'm an accountant/controller/CFO (semi-retired) and would indeed prefer use pivot tables for the kind of analyses you describe. No complicated formulas, built in time intelligence and much more. Or, with modern Excel, functions like PIVOTBY and GROUPBY offer very good formula alternatives.
I've looked at your schedule and note that you have left several 'loose ends' (red markings, question marks etc.) and I dare mentioning (no offence) that most of the formulas you propose are not very dynamic and do not easily allow for expanding data sets or additional options in the various columns. For example, add BMW to you list of car brands and almost all your formulas break. It would require manual insertion of columns to resolve spill errors. Not something I would recommend as an alternative to pivot tables. But, that's just my personal opinion.
Hello. Yes well I understand what you're saying. It is to show how to in a Formula, a progress in works, TAB with red question is a 1 CELL approach for all 4 SPILLS.
As a comparison to articles those are generally very individually instances that stop @ multi compiling together. Hence file, it is a simple suggestion answer to a complex question, for ppl who want a solution easily comprehended, ie:
The Question of: How to refer to A SPILL with MULTIPLE COLUMNS but there is only 1 SPILL REF.
Hence this puts those aspects together in a meaningful way. Pivot Tables or Sortby are definately probably professionals preferred modern method.
However a 4 cell Formula equivalent is of use!
FORMULA of 4 cell alternative to Pivot Table is finished now, added:
4 CRITERIA SUMIFS calc of SAME SPILL with INDEX of MULTI COLUMNS SPILL + DYNAMIC SUB TOTAL + TOTAL
# DYNAMIC HEADER
# DYNAMIC COLUMN
# DYNAMIC MAIN SUM TBL
# DYNAMIC SUB TOTAL + TOTAL
see new 4TH TAB! Also solution via Index is not something use frequently, apart from previously for mass Legacy XL Data Validation, which these days is alot easier with UNIQUE etc.
Explained in more detail HERE: