Calculating sums based on lookups in two different tables

[FDIOPY]

Hi all,

I am trying to complete a problem where I have some Items that have both properties and values assigned to them. I would like to sum all values for each property. However, the items are assigned properties and values in different tables, as such:

Items & Properties

Item	Property
A	Circle
B	Square
C	Triangle
D	Square
E	Circle
F	Triangle
G	Circle

And:

Items & Values

Item	Value
F	6​
G	7​
E	5​
C	3​
A	1​
D	4​
B	2​

Due to the nature of the data and the format in which it is received, I cannot create any intermediary cells or additional columns, e.g. using a VLOOKUP to assign properties to the second table and a further SUMIF to sum these by property. These two tables need to stay separate and the process needs to be automated as it will be refreshed fairly frequently so requires little to no manual intervention this way.

Sum table for properties

Properties	Sum calc'd
Circle
Square
Triangle

I have been stumped on this for a little while now and have tried a mix of SUMIF, SUMIFS, VLOOKUP, INDEX(MATCH,MATCH) to no success, so if anyone can let me know what formula/mix of formulae I should be using to achieve this that would help me a great deal!!

Thanks.

 

[StephenCrump]

Welcome to the Forum!

Will this work for you?

	A	B	C	D	E	F
1
2		Item	Property		Item	Value
3		A	Circle		F	6
4		B	Square		G	7
5		C	Triangle		E	5
6		D	Square		C	3
7		E	Circle		A	1
8		F	Triangle		D	4
9		G	Circle		B	2
10
11		Properties	Sum calc'd
12		Circle	13
13		Square	6
14		Triangle	9
15
Sheet1

Cell Formulas
Range	Formula
C12:C14	C12	=SUMPRODUCT(IFERROR(VLOOKUP(IF(C$3:C$9=B12,B$3:B$9),E$3:F$9,2,),0))

Or as tables:

	A	B	C	D	E	F
1		tProperty			tValue
2		Item	Property		Item	Value
3		A	Circle		F	6
4		B	Square		G	7
5		C	Triangle		E	5
6		D	Square		C	3
7		E	Circle		A	1
8		F	Triangle		D	4
9		G	Circle		B	2
10
11		Properties	Sum calc'd
12		Circle	13
13		Square	6
14		Triangle	9
15
Sheet1

Cell Formulas
Range	Formula
C12:C14	C12	=SUMPRODUCT(IFERROR(VLOOKUP(IF(tProperty[Property]=[@Properties],tProperty[Item]),tValue,2,),0))

 

[FDIOPY]

Hi Stephen,

That is very helpful, thank you. I have tested this out on Office 365 and it works well, however I should have specified that I am trying to get this function work within the Office 2013 environment, apologies for this. I have tried this out in that environment and it does not seem to return the desired sum values, although I Control+Shift+Enter'd the formula across a couple of cells and it then seemed to work as an array, not sure why and this is not entirely practical although is a step in the right direction. Any advice you might be able to give on adapting this to Office 2013 would be much appreciated!

 

[StephenCrump]

I should have specified that I am trying to get this function work within the Office 2013 environment ....

No problem. Your account details say 2013, which is what I assumed.

Unfortunately, I don't have access to older versions of Excel to test, but I thought that would work, i.e. that the SUMPRODUCT would be sufficient to make it a non-array formula.

Does this behave any better in 2013?

	A	B	C	D	E	F
1
2		Item	Property		Item	Value
3		A	Circle		F	6
4		B	Square		G	7
5		C	Triangle		E	5
6		D	Square		C	3
7		E	Circle		A	1
8		F	Triangle		D	4
9		G	Circle		B	2
10
11		Properties	Sum
12		Circle	13
13		Square	6
14		Triangle	9
Sheet1

Cell Formulas
Range	Formula
C12:C14	C12	=SUMPRODUCT(VLOOKUP(B$3:B$9,E$3:F$9,2,),--(C$3:C$9=B12))

 

[FDIOPY]

Unfortunately it returns #VALUE! error now! Making it an array formula still works as before however.

 

[FDIOPY]

Hi all,

I found a solution: I used an array formula as such:

{=SUMPRODUCT(SUMIFS([VALUE RANGE],[ITEM RANGE2],IF([PROPERTY RANGE]=[LOOKUP PROPERTY],[ITEM RANGE1])))}