Sum IF Based on a Column That Can Be Changed

[KellyBailey]

Hello,

I will do my best to explain this...

I have a list of data like this on one tab:

[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Units[/TD]
[TD]Colour[/TD]
[TD]Shape[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]10[/TD]
[TD]Red[/TD]
[TD]Square[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]def[/TD]
[TD]15[/TD]
[TD]Red[/TD]
[TD]Circle[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]ghi[/TD]
[TD]40[/TD]
[TD]Orange[/TD]
[TD]Circle[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]jkl[/TD]
[TD]25[/TD]
[TD]Yellow[/TD]
[TD]Square[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]

I have a second tab that has in cell A1 a dta validation list that allows me to choose from a drop down the relevant column headers - eg Colour, Shape, Size. (I don't need any help with this bit as I have figured this out).
On this tab I also have a table which changes based on which criteria I have chosen. So for example if I choose Colour in A1 the table looks as so...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Colour
[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

But if I choose Size in A1 it looks like this...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Size
[/TD]
[TD]Units
[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I would like is the column headed units above that is blank to populate based on the table on the data sheet. This would be a sum so the answers would be in this example;

Red = 25
Orange = 40
L = 65

etc.

The issue is not the sumif, but more how I get excel to know which column to use as it's reference point.

Hope this makes sense and someone can help

Many Thanks

 

[DRSteele]

If I understand correctly, this should work.

The Purple cell A7 is controlled by a Data Validation rule and the cells underneath it depend on what is selected in it.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E
1	SKU	Units	Colour	Shape	Size
2	abc	10	Red	Square	S
3	def	15	Red	Circle	M
4	ghi	40	Orange	Circle	L
5	jkl	25	Yellow	Square	L
6
7	Colour	Units
8	Red	25
9	Orange	40
10	Yellow	25
Sheet44

Cell Formulas
Range	Formula
B8	=IF(A8="","",SUMPRODUCT((INDEX($C$2:$E$5,,MATCH($A$7,$C$1:$E$1,0))=A8)*$B$2:$B$5))

[/FONT]