Hi,
I have a table where I'm trying to sum a quantity based on the value of the adjacent column. This is easy to do when you have one column for the range and the adjacent one for the quantity - and entries vertically. In my case, the quantity is always one column left from the type.
In column A2 I would expect: 10 (2+8)
In column B2 I would expect: 20
I've tried this formula, but it doesn't give the expected result:
=SUM(INDEX($C2:$K2,1,MATCH("newType",$C2:$K2,0)-1):INDEX($C2:$K2,1,MATCH("newType",$C2:$K2,0)-1))
Note: =COUNTIF($C2:$K2,"newType") gives me the number of occurrence of the right product, but not the quantity.
I have a table where I'm trying to sum a quantity based on the value of the adjacent column. This is easy to do when you have one column for the range and the adjacent one for the quantity - and entries vertically. In my case, the quantity is always one column left from the type.
| Number NewType | Number OldType | product | quantity | type | product | quantity | type | product | quantity | type |
| A2 - How many newType? | B2 - How many oldType? | P1 | 2 | newType | P2 | 20 | oldType | P3 | 8 | newType |
In column A2 I would expect: 10 (2+8)
In column B2 I would expect: 20
I've tried this formula, but it doesn't give the expected result:
=SUM(INDEX($C2:$K2,1,MATCH("newType",$C2:$K2,0)-1):INDEX($C2:$K2,1,MATCH("newType",$C2:$K2,0)-1))
Note: =COUNTIF($C2:$K2,"newType") gives me the number of occurrence of the right product, but not the quantity.
