# VBA - Sum Quantity based on Unique IDs

A

#### Alex

I'll need to do a sum of Col E based on criteria Col B - Unique, Col D -
Summing up its corresponding Col E values tabulated on an existing worksheet
(Sheet 1). Col A and Col C should not be in

a single ID.

Sheet 2
Col A| Col B| Col C| Col D| Col E|
xxxxx| ID1 | xxxxx| Acc| 100
xxxxx| ID1 | xxxxx| MP | 250
xxxxx| ID2 | xxxxx| MP | 50
xxxxx| ID3 | xxxxx| Acc | 60
xxxxx| ID4 | xxxxx| Acc | 150
xxxxx| ID5 | xxxxx| MP | 200
xxxxx| ID1 | xxxxx| MP | 200
xxxxx| ID2 | xxxxx| Acc | 50

* Actual no of rows varies but column is fixed.

Result:
Sheet 1
Col A| Col B| Col C| Col D| Col E|
xxxxx| ID1 | xxxxx| Acc| 100
xxxxx| ID1 | xxxxx| MP | 450
xxxxx| ID2 | xxxxx| MP | 50
xxxxx| ID2 | xxxxx| Acc | 110

* ID4/5 data was removed as it didn't satisfy the rule that Col D should
contain Acc and MP

A

#### Alex

Correction...Col A and Col C should not be shown.

Col B| Col D| Col E|
ID1 | Acc| 100
ID1 | MP | 450
ID2 | MP | 50
ID2 | Acc | 110

M

#### Mike H

Hir,

Create this on Sheet 1
Col A Col B Col C Col D
xxxx ID1 xxxx Acc
xxxx ID1 xxxx MP
xxxx ID2 xxxx MP
xxxx ID2 xxxx Acc

Put this in E1 and drag down
=SUMPRODUCT((Sheet2!\$B\$1:\$B\$8=B1)*(Sheet2!\$D\$1:\$D\$8=D1)*(Sheet2!\$E\$1:\$E\$8))

Mike

A

#### Alex

Hi Mike...thanks. I need this to be done in VBA. Can an "intellgience" built
in to extract the correct data (i.e. ID1 and ID2 data) among the whole of
sheet2?