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

Please help with the exclusion of row that doesn't contain Acc and MP within
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

Thanks in advance.
 
Ad

Advertisements

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
 
Ad

Advertisements

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top