Hello all
I need help now
The first 4 cells of the range J4:W4 have some data (numbers 1 to 4). I have a table and I need the product between the prices of the items in the range J4:W4 where in a column the value is 1.
Example=
<tbody>
</tbody>
Lets say that in W5 I need the product of K6:K19 only for any Kn where the value in Ln is 0 and the number in J is somewhere in J4:W4 (so I don't need K11 to be multiplied because 6 is not in the row J4:W4).
I tried with:
but it doesnt work. Any help?
Thanks
I need help now
The first 4 cells of the range J4:W4 have some data (numbers 1 to 4). I have a table and I need the product between the prices of the items in the range J4:W4 where in a column the value is 1.
Example=
J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |
4 | 1 | 2 | 3 | 4 | ||||||||||
5 | ||||||||||||||
6 | 1 | data | 0 | |||||||||||
7 | 2 | data | 0 | |||||||||||
8 | 3 | data | 1 | |||||||||||
9 | 4 | data | 1 | |||||||||||
10 | 5 | data | 0 | |||||||||||
11 | 6 | data | 1 | |||||||||||
12 | 7 | data | 0 | |||||||||||
13 | 8 | data | 0 | |||||||||||
14 | 9 | data | 0 | |||||||||||
15 | 10 | data | 1 | |||||||||||
16 | 11 | data | 0 | |||||||||||
17 | 12 | data | 0 | |||||||||||
18 | 13 | data | 1 | |||||||||||
19 | 14 | data | 1 |
<tbody>
</tbody>
Lets say that in W5 I need the product of K6:K19 only for any Kn where the value in Ln is 0 and the number in J is somewhere in J4:W4 (so I don't need K11 to be multiplied because 6 is not in the row J4:W4).
I tried with:
Code:
{=PRODUCT(IF(OFFSET(J6, MATCH(J4:W4, J6:J19),6)=0, K6:K19))*AC4}
but it doesnt work. Any help?
Thanks