sum of multiple ranges andmultiple conditions...

[Herman56]

I stumbled into a problem, I was sure of I could solve it easily... I a
fooling around now for a few days already, and I still could not figur
out how to solve this...

I got three tables.

Table_1 is nothing more then a list of items. That list is open t
changes, and is as well made a Named List. There is a simular list fo
each category of items; at this moment there are 6 categories, making
simular tables.

Table_2 is the "primary" table and has got the following (partial
design:

A = item listing
B = quality (AAA, AA, A, B, C, etc.)
C = level
D = upgrade/downgrade level
E = rating
F = % rating
G = price
H = % price

Table_3 is again in 6 different tastes. Each table is split into thre
parts:

part A = total sum of all different prices per item
part B = total sum of all different prices per quality
part C = total sum of all different prices per level

The formula for part A was the easiest:

=SUMIF(Table_2!$A$6:$A$5000;$A2;Table_2!$G$6:$G$5000)


But now part B and C:

I tried
=SUMPRODUCT(--(Table_2!$A$6:$A$5000=Table_1!$A$2:$A$5)*(Table_2!$B$6:$B$5000=$A10)*(Table_2!$G$6:$G$5000))

But this does not work the way I imagined it would... I only want
combined list of all items in Table_1 to be checked against Table_2
and then to count only the prices per quality or per level together
When I just pick one item, my formula is working, but that is not th
outcome I need.

I know I have solved simular problems in the past, but I canno
visualise a SUMIF or VLOOKUP formula to solve this problem of mine...
have tried several possible solutions, but am still coming back to th
SUMPRODUCT formula... Who can help me out?

Thanks in advance for any help!

Pau

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Table_2!$A$6:$A$5000,Table_1!$A$2:$A$5,0))),--(Table_2!$B$6:$B$5000=$A10),Table_2!$G$6:$G$5000)

Biff

 

[Herman56]

It works the way it was intended... Thanks! I had been incorporatin
MATCH already in an earlier stadium, but I was combining it with INDEX
and with ISNA... Problem solved! :-D

Pau