sum of multiple ranges andmultiple conditions...

[Herman56]

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$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 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

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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$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 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
>

[Herman56]

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