Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
Hi there,
I've got following constellation: A1 to C4 is a little database in A6, I put in a name like CAIRO in A7, I put in a description like CA M 4 Whenever CA is part of the description, the formula must give me C817 as result in cell A9. My problem is, that 'CA' is only a part of the description. A B C 1 CAIRO PB M 6 C816 2 CAIRO CA M 4 C817 3 CAIRO PB M 3 C816 4 CAIRO CA M 2 C817 5 6 CAIRO 7 CA M 4 8 9 =SUMPRODUCT(((A1:A3=A6)*(B1:B3=A7))*C1:C3) Can anyone help me please? Thanks a lot Norbert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX(C1:C4,MATCH((A1:A4=A6)*(B1:B4=A7),0)) -- Regards Frank Kabel Frankfurt, Germany Norbert Jaeger wrote: Hi there, I've got following constellation: A1 to C4 is a little database in A6, I put in a name like CAIRO in A7, I put in a description like CA M 4 Whenever CA is part of the description, the formula must give me C817 as result in cell A9. My problem is, that 'CA' is only a part of the description. A B C 1 CAIRO PB M 6 C816 2 CAIRO CA M 4 C817 3 CAIRO PB M 3 C816 4 CAIRO CA M 2 C817 5 6 CAIRO 7 CA M 4 8 9 =SUMPRODUCT(((A1:A3=A6)*(B1:B3=A7))*C1:C3) Can anyone help me please? Thanks a lot Norbert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
=INDEX(C1:C3,SMALL(IF((A1:A3=A6)*(LEFT(B1:B3,2)=LE FT(A7,2)),ROW(C1:C3)),1),1
) Entered with Ctrl+Shift+Enter rather than just enter since this is an array formula. -- Regards, Tom Ogilvy "Norbert Jaeger" wrote in message ... Hi there, I've got following constellation: A1 to C4 is a little database in A6, I put in a name like CAIRO in A7, I put in a description like CA M 4 Whenever CA is part of the description, the formula must give me C817 as result in cell A9. My problem is, that 'CA' is only a part of the description. A B C 1 CAIRO PB M 6 C816 2 CAIRO CA M 4 C817 3 CAIRO PB M 3 C816 4 CAIRO CA M 2 C817 5 6 CAIRO 7 CA M 4 8 9 =SUMPRODUCT(((A1:A3=A6)*(B1:B3=A7))*C1:C3) Can anyone help me please? Thanks a lot Norbert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Problem (AB) | Excel Discussion (Misc queries) | |||
Sumproduct problem | Excel Discussion (Misc queries) | |||
SUMPRODUCT Problem | New Users to Excel | |||
Problem with SumProduct | Excel Discussion (Misc queries) | |||
sumproduct problem | New Users to Excel |