Matrix formula ? Second try (no image)
 

Hi,

I need your help to solve a small formula problem.

A B C D E F G H I J K

1 0 1 3 4 5 9 11 12 = 3
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 0
10 1
11 0
12 1
13 0
14 0

I need to count in C1:I1 range, the number of times a given number appears
next to a "1" in the two

columns A1:B14

In this exemple, the answer is 3 because only the 4, 5 and 12 are associated
with a "1" in the adjacent

column.

Thanks.

Pierre Archambault

Matrix formula ? Second try (no image)
 

Hi Pierre

this is the neatest solution i could come up with (but there might be a
neater solution out there)

=SUMPRODUCT((--(VLOOKUP(C1,$A$1:$B$14,2,FALSE)))+(--(VLOOKUP(D1,$A$1:$B$14,2
,FALSE)))+(--(VLOOKUP(E1,$A$1:$B$14,2,FALSE)))+(--(VLOOKUP(F1,$A$1:$B$14,2,F
ALSE)))+(--(VLOOKUP(G1,$A$1:$B$14,2,FALSE)))+(--(VLOOKUP(H1,$A$1:$B$14,2,FAL
SE)))+(--(VLOOKUP(I1,$A$1:$B$14,2,FALSE))))

Cheers
JulieD

"Pierre Archambault" wrote in message
...
Hi,

I need your help to solve a small formula problem.

A B C D E F G H I J K

1 0 1 3 4 5 9 11 12 = 3
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 0
10 1
11 0
12 1
13 0
14 0

I need to count in C1:I1 range, the number of times a given number appears
next to a "1" in the two

columns A1:B14

In this exemple, the answer is 3 because only the 4, 5 and 12 are
associated
with a "1" in the adjacent

column.

Thanks.

Pierre Archambault

Matrix formula ? Second try (no image)
 

Kinda' quirky, but

=SUM(VLOOKUP({1,3,4,5,9,11,12},A2:B14,2,0))

array entered into two cells, will return the desired result to each cell.

Alan Beban

Pierre Archambault wrote:

Hi,

I need your help to solve a small formula problem.

A B C D E F G H I J K

1 0 1 3 4 5 9 11 12 = 3
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 0
10 1
11 0
12 1
13 0
14 0

I need to count in C1:I1 range, the number of times a given number appears
next to a "1" in the two

columns A1:B14

In this exemple, the answer is 3 because only the 4, 5 and 12 are associated
with a "1" in the adjacent

column.

Thanks.

Pierre Archambault

Matrix formula ? Second try (no image)
 

Hi Pierre

One more option:

In K1:

=SUMPRODUCT(LOOKUP($C1:$I1,$A$1:$A$14,$B$1:$B$14))

Copy K1 down with the fill handle (the little square in the lower
right corner of the cell) if necessary.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pierre Archambault" skrev i en meddelelse
...
Hi,

I need your help to solve a small formula problem.

A B C D E F G H I J K

1 0 1 3 4 5 9 11 12 = 3
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 0
10 1
11 0
12 1
13 0
14 0

I need to count in C1:I1 range, the number of times a given number appears
next to a "1" in the two

columns A1:B14

In this exemple, the answer is 3 because only the 4, 5 and 12 are
associated
with a "1" in the adjacent

column.

Thanks.

Pierre Archambault

Matrix formula ? Second try (no image)
 

Addendum:

In my first formula, A1:A14 must be in ascending order.
If this isn't appropiate, try this one instead:

=SUMPRODUCT(N(OFFSET($A$1,MATCH($C1:$I1,$A$1:$A$14 ,0)-1,1)))

LeoH


"Leo Heuser" skrev i en meddelelse
...
Hi Pierre

One more option:

In K1:

=SUMPRODUCT(LOOKUP($C1:$I1,$A$1:$A$14,$B$1:$B$14))

Copy K1 down with the fill handle (the little square in the lower
right corner of the cell) if necessary.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pierre Archambault" skrev i en
meddelelse
...
Hi,

I need your help to solve a small formula problem.

A B C D E F G H I J K

1 0 1 3 4 5 9 11 12 = 3
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 0
10 1
11 0
12 1
13 0
14 0

I need to count in C1:I1 range, the number of times a given number
appears
next to a "1" in the two

columns A1:B14

In this exemple, the answer is 3 because only the 4, 5 and 12 are
associated
with a "1" in the adjacent

column.

Thanks.

Pierre Archambault

Matrix formula ? Second try (no image)
 

"Pierre Archambault" wrote...
....
I need to count in C1:I1 range, the number of times a given number
appears next to a "1" in the two

columns A1:B14
....

If B1:B14 would only contain zeros or ones, either

=SUMPRODUCT(SUMIF(A1:A14,C1:I1,B1:B14))

or

=SUMPRODUCT(COUNTIF(C1:I1,A1:A14),B1:B14)

Unlike LOOKUP-based formulas, if there were duplicate values in A1:A14, the
formulas above would count multiple times. That may be good or bad.

Matrix formula ? Second try (no image)
 

Thank you very much.

This solution is really what I was looking for.


"Harlan Grove" a écrit dans le message de
...
"Pierre Archambault" wrote...
...
I need to count in C1:I1 range, the number of times a given number
appears next to a "1" in the two

columns A1:B14
...

If B1:B14 would only contain zeros or ones, either

=SUMPRODUCT(SUMIF(A1:A14,C1:I1,B1:B14))

or

=SUMPRODUCT(COUNTIF(C1:I1,A1:A14),B1:B14)

Unlike LOOKUP-based formulas, if there were duplicate values in A1:A14,
the
formulas above would count multiple times. That may be good or bad.