Doing an operation before SUMIF or MATCH-ing multiple values to multiply before summing-CodePudding

I have an Excel document with multiple small tables in it. Here, each table describes a single project, and at the end of the worksheet, I want to create a summary that doesn't have "hardcoded" locations so that that when the amount of projects (tables) is adjusted, it doesn't break all the formulas. Basically, it looks like this:

    A                           B
1   Project 1
2      Units                    200
3      Price / Unit             10
4      Material / Unit          5
5      Handling / Unit          1
6      Total cost               6
7      Profit Margin / Unit     4
8
9  Project 2
10     Units                    100
11     Price / Unit             5
12     Material / Unit          1
13     Handling / Unit          1
14     Total cost               2
15     Profit Margin / Unit     3
16 
...

19 Summary
20     Units                    =SUMIF( A$1:A19 ; A20 ; B$1:B19 )
21     Material costs           ???
22     Handling costs           ???
23     Total Profit             ???

Here, there may be an arbitrary amount of projects and I'm unsure how to create a formula that directly calculates the total material costs (and by the same pattern, Handling and Total Profit). For the total units, I can simply use a =SUMIF( A$1:A19 ; A20 ; B$1:B19 ) instead of =B2 B2 by having the function search col A for the keyword "Units" but in order to do this for the total material costs, I need to multiply first. Eg, it would be =B2*B4 B10*B12.

My first idea was to use an INDEX MATCH approach to extract a subarray from each table and then sum it all up using SUMPRODUCT however the MATCH function unfortunately only returns the first result and I can't get it to output an array of results (I think this is just a limitation with the function?).

I guess it would also be possible to simply add extra lines to each table to pre-calculate these products, but I don't like that solution as it would give the tables a lot of unnecessary extra bloat and I'd really like to solve this in one formula.

Any help would be greatly appreciated!

CodePudding user response：

Only way I can see to do it is to Change your tables so that the values go across with only 1 line per project. You would then be able to use the sumproduct formula

CodePudding user response：

I came up with a dirty solution, using SUMPRODUCT formula that works if your using Office 365 :

example calculating total handling cost:

=SUMPRODUCT(FILTER(B:B,A:A="Unit"),FILTER(B:B,A:A="Handling / Unit"))

FILTER(B:B,A:A="Unit"), returns an array of values on the right of cells containing "Unit"
FILTER(B:B,A:A="Handling / Unit"), returns an array of values on the right of cells containing "Handling / Unit"