Array formula? Please help

22764636

New Member
Joined
Oct 24, 2013
Messages
10
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
1712
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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),""))
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top