sumproduct with partial charcter matching

M

Matt

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
 
M

Max

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
 
S

smartin

Matt said:
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
Click to expand...

This works, but I don't think it's an A+ solution (if you will pardon
the pun) as it takes advantage of the fact that your example data always
has a space between the ABO and Rh, which might not actually be the case.

With Data Table on sheet "Data" and the following arrangement on another
sheet:

| A B C D E
--+-------------------------------
1 | A AB B O
2 | Inventory
3 | Issues

In cell B2, fill right and down:

=SUMPRODUCT(--($A2=Data!$A$2:$A$17),--(B$1=TRIM(LEFT(Data!$C$2:$C$17,2))),(Data!$D$2:$D$17))

Maybe someone can fix the bad blood in my offering.
 

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

Similar Threads

Sumproduct - meet multiple criteria from a list 6
After I reset my Games they get scambled. Conditional Formatting orMacro messed up somewhere 1
SumProduct or sumif 4
Cell values not being properly evaluated by VLOOKUP() orSUMPRODUCT()... why? 1
Counting cells with SUMPRODUCT and conditionals...? 1
Sumproduct Search, Sum Durations 3
Multiple Lookup as condition in sumproduct formula 2
Problem with a SUMPRODUCT Formula 8

Top