Sumproduct help with a difference

[k.mca]

hi All,

i understand how sumproduct works but i want to return the column
number of the first true(1) citriea within the sumproduct function.
see below example

Result row 2 4 6 7 5 6 6 (range
is a2:a7)
Data row 2 3 4 3 3 4 2 (range is
b2:b7)

=sumproduct(--(b2:b7=3),--(a2:a7)) this formula will return
=sumproduct({0,1,0,1,1,0,0}, {2,4,6,7,5,6,6}) and the answer of 16

what i want to do is work out formula that will return the only the
3rd (1(True) within the sumproduct function) value therefore the
answer should be 7.

any thoughts??

 

[k.mca]

sorry guys,

i want the formula linked to cell c2, if i change c3 to 1 c2 will
return 4, if i change c3 to 2 c2 should show 7 and if i change c3 to 3
c2 should show 5.

hope it makes sense

 

[smartin]

hi All,

i understand how sumproduct works but i want to return the column
number of the first true(1) citriea within the sumproduct function.
see below example

Result row 2 4 6 7 5 6 6 (range
is a2:a7)
Data row 2 3 4 3 3 4 2 (range is
b2:b7)

=sumproduct(--(b2:b7=3),--(a2:a7)) this formula will return
=sumproduct({0,1,0,1,1,0,0}, {2,4,6,7,5,6,6}) and the answer of 16

what i want to do is work out formula that will return the only the
3rd (1(True) within the sumproduct function) value therefore the
answer should be 7.

any thoughts??

Here's one way (using helper formulae). This uses cell C3 as the
"selector" as mentioned in your follow-up.

D2 =--($B2=3) (fill down)
E2 =IF(AND($D2,SUM($D$22)=$C$3),$A2,"") (fill down)
F2 =MAX($E$2:$E$8) (final result)

 

[Max]

Your data as originally posted is assumed in A2:B8, viz:

2 2
4 3
6 4
7 3
5 3
6 4
6 2


The input cell for the variable is C3, eg: 1, 2 , 3

Place this in C2, array-enter the formula,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7)<>0,ROW(1:7)),C3))

C2 will return the results that you seek, as described below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[smartin]

Your data as originally posted is assumed in A2:B8, viz:

2 2
4 3
6 4
7 3
5 3
6 4
6 2


The input cell for the variable is C3, eg: 1, 2 , 3

Place this in C2, array-enter the formula,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7)<>0,ROW(1:7)),C3))

C2 will return the results that you seek, as described below

I was hoping someone would post an array version because I could not get
my head around it (though it makes perfect sense now).

It looks like this could be simplified a little:

=INDEX(A2:A8,SMALL(IF((B2:B8=3)*ROW(1:7),ROW(1:7)),C3))

Nice work!

 

[Max]

Thanks for the compliments, and the refinement!
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik