More than 1 Variables

[Guest]

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!

 

[T. Valko]

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))),D115)

Biff