I want to be able to sum columns until a Condition is met, but also be able to peek them based on their headers.
My partial pivot:
| WC 11 | WC 12 | WC 13 | WC 21 | WC 22 | WC 23 | WC 31 | WC 32 | WC 33 | WC 41 | WC 42 | WC 43 | WC 44 | WC 51 | WC 61 | WC 62 | WC 63 | WC 64 | WC 71 | WC 72 | WC 73 | WC 74 | WC 76 | WC 77 | WC 81 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 37 Order-1 | 4.9 | ||||||||||||||||||||||||
| 37 Order-2 | 0.7 | ||||||||||||||||||||||||
| 32 Order-1 | 0 | ||||||||||||||||||||||||
| 32 Order-10 | 10.75 | ||||||||||||||||||||||||
| 32 Order-11 | 1.5 | ||||||||||||||||||||||||
| 32 Order-12 | 2.2 | ||||||||||||||||||||||||
| 32 Order-13 | 2.2 | ||||||||||||||||||||||||
| 32 Order-14 | 1.9 | ||||||||||||||||||||||||
| 32 Order-15 | 0.7 | ||||||||||||||||||||||||
| 32 Order-16 | 0 | ||||||||||||||||||||||||
| 32 Order-17 | 7.1 | ||||||||||||||||||||||||
| 32 Order-18 | 0.3 |
Expected output (BLUE):
I tried to merge SUM, MATCH, and INDEX, but left with no solution.
English is not my native language! I would be happy if you would edit the question so it could be more readable. Thanks!
CodePudding user response：
Use SUM and two index matches:
=SUM(INDEX($B$2:$Z$2,,MATCH(B17,$B$1:$Z$1,0)):INDEX($B$2:$Z$13,MATCH(A17,$A$2:$A$13,0),MATCH(B17,$B$1:$Z$1,0)))
