#### 22764636

##### New Member
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=
 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

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try this confirmed with Ctrl+Shift+Enter instead of just Enter:

=PRODUCT(IFERROR(IF(ROW(J6:J19)-ROW(\$J\$6)+1=TRANSPOSE(MATCH(J4:M4,J6:J19,0)),K6:K19),""))

Nevermind, the above formula is not working correctly.

If i understood correctly....

=PRODUCT(IF(\$L\$6:\$L\$19=0,IF(ISNUMBER(MATCH(\$J\$6:\$J\$19,\$J\$4:\$W\$4,0)),\$K\$6:\$K\$19,1),1))

Greetings

Edit: this is CSE formula so CTRL+SHIFT+ENTER

Last edited:
If i understood correctly....

=PRODUCT(IF(\$L\$6:\$L\$19=0,IF(ISNUMBER(MATCH(\$J\$6:\$J\$19,\$J\$4:\$W\$4,0)),\$K\$6:\$K\$19,1),1))

Greetings

Edit: this is CSE formula so CTRL+SHIFT+ENTER

thanks mate! It works but I've already found an issue. If in \$J\$4:\$W\$4 I have 1,2,3,5 it doesn't work. Can you find out any reason?

thanks mate! It works but I've already found an issue. If in \$J\$4:\$W\$4 I have 1,2,3,5 it doesn't work. Can you find out any reason?

Works fine for me. Perhaps you can clarify what you mean by "doesn't work".

Regards

Works fine for me. Perhaps you can clarify what you mean by "doesn't work".

Regards

I am sorry. I am mistaken. It works like a charm. Thanks again to all of you.

could you please explain step by step how does this function works? thanks

Replies
1
Views
274
Replies
11
Views
665
Replies
5
Views
199
Replies
15
Views
469
Replies
3
Views
443

1,219,578
Messages
6,149,097
Members
450,859
Latest member
njaitley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back