Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 1 Variables
Question: Is there a way to match and sum 2 or more variables in one formula?
Sheet 1 ------A----------B---------------C---------D-$----------E--- 1----GH1JRPA----11762---- Oct-06----2,000.00---- AX-01 * 2----GH1JRPA----11763---- Oct-06----2,000.00---- AX-01 3----GH1JRPA----11831---- Oct-06----2,000.00---- AX-01 4----RK1JSKA----12227---- Oct-06----5,000.00---- CX-03 5----RK1JSKA----12234---- Oct-06----5,000.00---- CX-03 6----AK1JRKA----12934---- Oct-06----1,000.00---- CX-03 7----GD1JLPA----10880---- Oct-06----1,000.00---- BX-02 8----GD1JLPA----10879---- Oct-06----1,000.00---- AX-01 9----GH1JRPA----11761---- Oct-06----1,000.00---- AX-01 10---GH1JRPA----11762---- Oct-06----1,000.00---- BX-02 * 11---GH1JRPA----11763---- Oct-06----1,000.00---- BX-02 12---GH1JRPA----11831---- Oct-06----1,000.00---- CX-03 13---RK1JSKA----12227---- Oct-06----1,000.00---- CX-03 14---RK1JSKA----12234---- Oct-06----1,000.00---- CX-03 15---GH1JRPA----11762---- Oct-06----1,000.00---- CX-03 * Sheet 2 The Result: * 1----GH1JRPA-11762 #This is where the codes are entered (concatenate) 2----AX-01: 2,000.00 (sumIF) 3----BX-02: 1,000.00 4----CX-03: 1,000.00 My current method: Sheet 1: column F, concatenate(A1,"-",B1,"-",E1) = GH1JRPA-11762-AX-01 Sheet 2: SUMIF the concatenate above. and PIVOT TABLE. Is there a better way to do this? (say) Vlookup+Sumif+Match....and done!!! Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 1 Variables
Try this:
A20 = GH1JRPA B20 = 11762 C20:C23 = AX-01, BX-02,CX-03 =SUMPRODUCT(--(A1:A15=A20),--(B1:B15=B20),--(ISNUMBER(MATCH(E1:E15,C20:C22,0))),D1:D15) Biff " wrote in message ... Question: Is there a way to match and sum 2 or more variables in one formula? Sheet 1 ------A----------B---------------C---------D-$----------E--- 1----GH1JRPA----11762---- Oct-06----2,000.00---- AX-01 * 2----GH1JRPA----11763---- Oct-06----2,000.00---- AX-01 3----GH1JRPA----11831---- Oct-06----2,000.00---- AX-01 4----RK1JSKA----12227---- Oct-06----5,000.00---- CX-03 5----RK1JSKA----12234---- Oct-06----5,000.00---- CX-03 6----AK1JRKA----12934---- Oct-06----1,000.00---- CX-03 7----GD1JLPA----10880---- Oct-06----1,000.00---- BX-02 8----GD1JLPA----10879---- Oct-06----1,000.00---- AX-01 9----GH1JRPA----11761---- Oct-06----1,000.00---- AX-01 10---GH1JRPA----11762---- Oct-06----1,000.00---- BX-02 * 11---GH1JRPA----11763---- Oct-06----1,000.00---- BX-02 12---GH1JRPA----11831---- Oct-06----1,000.00---- CX-03 13---RK1JSKA----12227---- Oct-06----1,000.00---- CX-03 14---RK1JSKA----12234---- Oct-06----1,000.00---- CX-03 15---GH1JRPA----11762---- Oct-06----1,000.00---- CX-03 * Sheet 2 The Result: * 1----GH1JRPA-11762 #This is where the codes are entered (concatenate) 2----AX-01: 2,000.00 (sumIF) 3----BX-02: 1,000.00 4----CX-03: 1,000.00 My current method: Sheet 1: column F, concatenate(A1,"-",B1,"-",E1) = GH1JRPA-11762-AX-01 Sheet 2: SUMIF the concatenate above. and PIVOT TABLE. Is there a better way to do this? (say) Vlookup+Sumif+Match....and done!!! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RE-submitting of: constructing (complex) variables with worksheet functions | Excel Worksheet Functions | |||
Bubble Charts with two non-numerical variables | Excel Worksheet Functions | |||
VBA reseting variables | Excel Discussion (Misc queries) | |||
VBA reseting variables | Excel Discussion (Misc queries) | |||
Passing Variables | Excel Discussion (Misc queries) |