Q
Qull666
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!
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!