Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
from a series to a matrix using formula | Excel Discussion (Misc queries) | |||
Hyperlink to an image in other worksheet, displaying entire image. | Excel Worksheet Functions | |||
How to od a combined IF and AND in a matrix formula | Excel Worksheet Functions | |||
entering matrix formula | Excel Worksheet Functions | |||
Export the worksheet background image as an image file - possible? | Excel Programming |