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

I got three tables.

Table_1 is nothing more then a list of items. That list is open to
changes, and is as well made a Named List. There is a simular list for
each category of items; at this moment there are 6 categories, making 6
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 three
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$50 00)


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 a
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 the
outcome I need.

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

Thanks in advance for any help!

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile: http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=506899

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" wrote in
message ...

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

I got three tables.

Table_1 is nothing more then a list of items. That list is open to
changes, and is as well made a Named List. There is a simular list for
each category of items; at this moment there are 6 categories, making 6
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 three
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$50 00)


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 a
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 the
outcome I need.

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

Thanks in advance for any help!

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile:
http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=506899

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

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile: http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=506899