sumproduct with partial charcter matching
 

I'm trying to achieve the following summary based on the data in the 'Data
table' below. I require assitance to construct a sumproduct formula that will
aggregate the blood groups correctly (e.g. A + & A - combined as A, AB + & AB
- combined as AB). I want to construct one generic formula that will
aggregate based on the blood group characters excluding the +/-.

Summary Table
Combined Blood Groups
Data Type Component A AB B O
Inventory Red Cell 10 20 40 60
Issues Red Cell 2 4 6 8

Data Table
Data Type Component ABO Qty
Inventory Red Cell A + 5
Inventory Red Cell A - 5
Inventory Red Cell AB + 10
Inventory Red Cell AB - 10
Inventory Red Cell B + 20
Inventory Red Cell B - 20
Inventory Red Cell O + 30
Inventory Red Cell O - 30
Issues Red Cell A + 1
Issues Red Cell A - 1
Issues Red Cell AB + 2
Issues Red Cell AB - 2
Issues Red Cell B + 3
Issues Red Cell B - 3
Issues Red Cell O + 4
Issues Red Cell O - 4

sumproduct with partial charcter matching
 

Assume your data table is in sheet: x, with data in A1:D17
Assume your summary table (in another sheet) is set up with C1 across
housing the combined blood groups A, AB, B, O and A2:B2 down containing the
data type and component, put this in C2:
=SUMPRODUCT((x!$A$2:$A$17=$A2)*(x!$B$2:$B$17=$B2)* (TRIM(SUBSTITUTE(SUBSTITUTE(x!$C$2:$C$17,"+",""),"-",""))=C$1),x!$D$2:$D$17)
Copy C2 across / fill down for the required results

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Matt" wrote:
I'm trying to achieve the following summary based on the data in the 'Data
table' below. I require assitance to construct a sumproduct formula that will
aggregate the blood groups correctly (e.g. A + & A - combined as A, AB + & AB
- combined as AB). I want to construct one generic formula that will
aggregate based on the blood group characters excluding the +/-.

Summary Table
Combined Blood Groups
Data Type Component A AB B O
Inventory Red Cell 10 20 40 60
Issues Red Cell 2 4 6 8

Data Table
Data Type Component ABO Qty
Inventory Red Cell A + 5
Inventory Red Cell A - 5
Inventory Red Cell AB + 10
Inventory Red Cell AB - 10
Inventory Red Cell B + 20
Inventory Red Cell B - 20
Inventory Red Cell O + 30
Inventory Red Cell O - 30
Issues Red Cell A + 1
Issues Red Cell A - 1
Issues Red Cell AB + 2
Issues Red Cell AB - 2
Issues Red Cell B + 3
Issues Red Cell B - 3
Issues Red Cell O + 4
Issues Red Cell O - 4

sumproduct with partial charcter matching
 

Max thankyou very much for the prompt reply - this works perfectly!

"Max" wrote:

Assume your data table is in sheet: x, with data in A1:D17
Assume your summary table (in another sheet) is set up with C1 across
housing the combined blood groups A, AB, B, O and A2:B2 down containing the
data type and component, put this in C2:
=SUMPRODUCT((x!$A$2:$A$17=$A2)*(x!$B$2:$B$17=$B2)* (TRIM(SUBSTITUTE(SUBSTITUTE(x!$C$2:$C$17,"+",""),"-",""))=C$1),x!$D$2:$D$17)
Copy C2 across / fill down for the required results

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Matt" wrote:
I'm trying to achieve the following summary based on the data in the 'Data
table' below. I require assitance to construct a sumproduct formula that will
aggregate the blood groups correctly (e.g. A + & A - combined as A, AB + & AB
- combined as AB). I want to construct one generic formula that will
aggregate based on the blood group characters excluding the +/-.

Summary Table
Combined Blood Groups
Data Type Component A AB B O
Inventory Red Cell 10 20 40 60
Issues Red Cell 2 4 6 8

Data Table
Data Type Component ABO Qty
Inventory Red Cell A + 5
Inventory Red Cell A - 5
Inventory Red Cell AB + 10
Inventory Red Cell AB - 10
Inventory Red Cell B + 20
Inventory Red Cell B - 20
Inventory Red Cell O + 30
Inventory Red Cell O - 30
Issues Red Cell A + 1
Issues Red Cell A - 1
Issues Red Cell AB + 2
Issues Red Cell AB - 2
Issues Red Cell B + 3
Issues Red Cell B - 3
Issues Red Cell O + 4
Issues Red Cell O - 4

sumproduct with partial charcter matching
 

Thats good, you're welcome.
Do click the YES button below in that response, won't you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Matt" wrote:
Max thankyou very much for the prompt reply - this works perfectly!