Sumproduct
 

I have two tables:

Table 1:
Item Sales
I_1 5
I_2 7
I_1 3
I_3 6

Table 2:
Item Group
I_1 G_1
I_2 G_2
I_3 G_1

I have no problem to calculate sales of certain item
=SUMPRODUCT(--(A1:A5="I_1");B1:B5). But I dont know how to calculate sales of
items from certain group. I dont want to manipulate table 1 because I get one
table like this every day. Any help appreciated.

Sumproduct
 

Add a helper column C to Table 1, insert this formula in C2:
=VLOOKUP(A2,Table2!A:B,2,FALSE)

Drag it down to C5 (or as it's necessary)!

Create Table 3:
Groups Sales by group
G_1 14(formula here)
G_2 7

Insert in B2:
=SUMPRODUCT(--(Table1!$C$2:$C$5=A2),Table1!$B$2:$B$5)
Drag it down as necessary!

Regards,
Stefi

€žOscar€� ezt Ã*rta:

I have two tables:

Table 1:
Item Sales
I_1 5
I_2 7
I_1 3
I_3 6

Table 2:
Item Group
I_1 G_1
I_2 G_2
I_3 G_1

I have no problem to calculate sales of certain item
=SUMPRODUCT(--(A1:A5="I_1");B1:B5). But I dont know how to calculate sales of
items from certain group. I dont want to manipulate table 1 because I get one
table like this every day. Any help appreciated.

Sumproduct
 

Thanks Stefi, but I'm trying to find a way to do it without changing Table 1.
I already wrote macro that adds "vlookup" column to Table 1, but if it is
possible to get a result without changing Table 1 (using formula) my life
would be much easier. (Actually I wrote function that works fine but it is
quiet slow so I'm trying to find a way to do it using excel functions.)

Thanks anyway,
Oscar.

"Stefi" wrote:

Add a helper column C to Table 1, insert this formula in C2:
=VLOOKUP(A2,Table2!A:B,2,FALSE)

Drag it down to C5 (or as it's necessary)!

Create Table 3:
Groups Sales by group
G_1 14(formula here)
G_2 7

Insert in B2:
=SUMPRODUCT(--(Table1!$C$2:$C$5=A2),Table1!$B$2:$B$5)
Drag it down as necessary!

Regards,
Stefi

€žOscar€� ezt Ã*rta:

I have two tables:

Table 1:
Item Sales
I_1 5
I_2 7
I_1 3
I_3 6

Table 2:
Item Group
I_1 G_1
I_2 G_2
I_3 G_1

I have no problem to calculate sales of certain item
=SUMPRODUCT(--(A1:A5="I_1");B1:B5). But I dont know how to calculate sales of
items from certain group. I dont want to manipulate table 1 because I get one
table like this every day. Any help appreciated.

Sumproduct
 

If Table 1 is in A1:B4 and Table 2 is in D1:E3 (and is sorted per your
example), try:

=SUMPRODUCT(--(LOOKUP(A1:A4,D1:D3,E1:E3)=H1),B1:B4)


"Oscar" wrote:

Thanks Stefi, but I'm trying to find a way to do it without changing Table 1.
I already wrote macro that adds "vlookup" column to Table 1, but if it is
possible to get a result without changing Table 1 (using formula) my life
would be much easier. (Actually I wrote function that works fine but it is
quiet slow so I'm trying to find a way to do it using excel functions.)

Thanks anyway,
Oscar.

"Stefi" wrote:

Add a helper column C to Table 1, insert this formula in C2:
=VLOOKUP(A2,Table2!A:B,2,FALSE)

Drag it down to C5 (or as it's necessary)!

Create Table 3:
Groups Sales by group
G_1 14(formula here)
G_2 7

Insert in B2:
=SUMPRODUCT(--(Table1!$C$2:$C$5=A2),Table1!$B$2:$B$5)
Drag it down as necessary!

Regards,
Stefi

€žOscar€� ezt Ã*rta:

I have two tables:

Table 1:
Item Sales
I_1 5
I_2 7
I_1 3
I_3 6

Table 2:
Item Group
I_1 G_1
I_2 G_2
I_3 G_1

I have no problem to calculate sales of certain item
=SUMPRODUCT(--(A1:A5="I_1");B1:B5). But I dont know how to calculate sales of
items from certain group. I dont want to manipulate table 1 because I get one
table like this every day. Any help appreciated.

Sumproduct
 

Although if I_3 did not exist in table 2, lookup would match I_3 in table 1
to I_2 in table 2 and return an erronous result. Given the same assumptions
as to the location of Table 1 and Table 2 and say H1 = G_1, I would change my
suggestion to:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4&H1,D1:D3&E1:E3,0))),B1:B4)


"JMB" wrote:

If Table 1 is in A1:B4 and Table 2 is in D1:E3 (and is sorted per your
example), try:

=SUMPRODUCT(--(LOOKUP(A1:A4,D1:D3,E1:E3)=H1),B1:B4)


"Oscar" wrote:

Thanks Stefi, but I'm trying to find a way to do it without changing Table 1.
I already wrote macro that adds "vlookup" column to Table 1, but if it is
possible to get a result without changing Table 1 (using formula) my life
would be much easier. (Actually I wrote function that works fine but it is
quiet slow so I'm trying to find a way to do it using excel functions.)

Thanks anyway,
Oscar.

"Stefi" wrote:

Add a helper column C to Table 1, insert this formula in C2:
=VLOOKUP(A2,Table2!A:B,2,FALSE)

Drag it down to C5 (or as it's necessary)!

Create Table 3:
Groups Sales by group
G_1 14(formula here)
G_2 7

Insert in B2:
=SUMPRODUCT(--(Table1!$C$2:$C$5=A2),Table1!$B$2:$B$5)
Drag it down as necessary!

Regards,
Stefi

€žOscar€� ezt Ã*rta:

I have two tables:

Table 1:
Item Sales
I_1 5
I_2 7
I_1 3
I_3 6

Table 2:
Item Group
I_1 G_1
I_2 G_2
I_3 G_1

I have no problem to calculate sales of certain item
=SUMPRODUCT(--(A1:A5="I_1");B1:B5). But I dont know how to calculate sales of
items from certain group. I dont want to manipulate table 1 because I get one
table like this every day. Any help appreciated.

Sumproduct
 

Exactly what I was looking for!

Thanks a lot,
Oscar.

"JMB" wrote:

Although if I_3 did not exist in table 2, lookup would match I_3 in table 1
to I_2 in table 2 and return an erronous result. Given the same assumptions
as to the location of Table 1 and Table 2 and say H1 = G_1, I would change my
suggestion to:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4&H1,D1:D3&E1:E3,0))),B1:B4)


"JMB" wrote:

If Table 1 is in A1:B4 and Table 2 is in D1:E3 (and is sorted per your
example), try:

=SUMPRODUCT(--(LOOKUP(A1:A4,D1:D3,E1:E3)=H1),B1:B4)


"Oscar" wrote:

Thanks Stefi, but I'm trying to find a way to do it without changing Table 1.
I already wrote macro that adds "vlookup" column to Table 1, but if it is
possible to get a result without changing Table 1 (using formula) my life
would be much easier. (Actually I wrote function that works fine but it is
quiet slow so I'm trying to find a way to do it using excel functions.)

Thanks anyway,
Oscar.

"Stefi" wrote:

Add a helper column C to Table 1, insert this formula in C2:
=VLOOKUP(A2,Table2!A:B,2,FALSE)

Drag it down to C5 (or as it's necessary)!

Create Table 3:
Groups Sales by group
G_1 14(formula here)
G_2 7

Insert in B2:
=SUMPRODUCT(--(Table1!$C$2:$C$5=A2),Table1!$B$2:$B$5)
Drag it down as necessary!

Regards,
Stefi

€žOscar€� ezt Ã*rta:

I have two tables:

Table 1:
Item Sales
I_1 5
I_2 7
I_1 3
I_3 6

Table 2:
Item Group
I_1 G_1
I_2 G_2
I_3 G_1

I have no problem to calculate sales of certain item
=SUMPRODUCT(--(A1:A5="I_1");B1:B5). But I dont know how to calculate sales of
items from certain group. I dont want to manipulate table 1 because I get one
table like this every day. Any help appreciated.

Sumproduct
 

you're welcome. thanks for posting back.

"Oscar" wrote:

Exactly what I was looking for!

Thanks a lot,
Oscar.

"JMB" wrote:

Although if I_3 did not exist in table 2, lookup would match I_3 in table 1
to I_2 in table 2 and return an erronous result. Given the same assumptions
as to the location of Table 1 and Table 2 and say H1 = G_1, I would change my
suggestion to:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4&H1,D1:D3&E1:E3,0))),B1:B4)


"JMB" wrote:

If Table 1 is in A1:B4 and Table 2 is in D1:E3 (and is sorted per your
example), try:

=SUMPRODUCT(--(LOOKUP(A1:A4,D1:D3,E1:E3)=H1),B1:B4)


"Oscar" wrote:

Thanks Stefi, but I'm trying to find a way to do it without changing Table 1.
I already wrote macro that adds "vlookup" column to Table 1, but if it is
possible to get a result without changing Table 1 (using formula) my life
would be much easier. (Actually I wrote function that works fine but it is
quiet slow so I'm trying to find a way to do it using excel functions.)

Thanks anyway,
Oscar.

"Stefi" wrote:

Add a helper column C to Table 1, insert this formula in C2:
=VLOOKUP(A2,Table2!A:B,2,FALSE)

Drag it down to C5 (or as it's necessary)!

Create Table 3:
Groups Sales by group
G_1 14(formula here)
G_2 7

Insert in B2:
=SUMPRODUCT(--(Table1!$C$2:$C$5=A2),Table1!$B$2:$B$5)
Drag it down as necessary!

Regards,
Stefi

€žOscar€� ezt Ã*rta:

I have two tables:

Table 1:
Item Sales
I_1 5
I_2 7
I_1 3
I_3 6

Table 2:
Item Group
I_1 G_1
I_2 G_2
I_3 G_1

I have no problem to calculate sales of certain item
=SUMPRODUCT(--(A1:A5="I_1");B1:B5). But I dont know how to calculate sales of
items from certain group. I dont want to manipulate table 1 because I get one
table like this every day. Any help appreciated.