Sumifs or Sumproduct

[VbaHell]

Hi All

I am having an issue with trying to get a formula to work and hope that someone tell point me in the right direction please


Worksheets "Supplier Dashboard" Column "A:A" has supplier names
Worksheets "Supplier Dashboard" Column "BB:BB" has number values

D5 is a drop down box (Qtr1, Qtr2, Qtr3 or Qtr4)
V1 is the supplier name

[=SUMIFS('Supplier Dashboard'!BB:BB,'Supplier Dashboard'!A:A,V1,D5,"Qtr1")]

At the moment all I get is #Value but if I remove the last part of formula [D5,"Qtr1"] then it works fine.

Any idea's please

 

[AlanY]

which column are the Qtr1, Qtr2 etc reside?

 

[VbaHell]

BB:BB is the column to sum if you choose "Qtr1"
D5 is where you would choose "Qtr1" or "Qtr2" etc

 

[AlanY]

If BB are all for the Qtr1, then where are the numbers for Qtr2, 3 etc

 

[Aladin Akyurek]

What do you sum if D5 = Qtr2?

The logic can run like this...

=SUMIFS(INDEX('Supplier Dashboard'!BB:EE,0,MATCH(D5,{"Qtr1","Qtr2","Qtr3","Qtr4"},0)),'Supplier Dashboard'!A:A,V1)]

 

[AlanY]

have you got a column similar to C below?

Excel 2013/2016
	A	B	C	D	E	F
1	supplier names	values	Qtr?
2	Name3	900	Qtr1	Name2	Qtr1	1400
3	Name1	200	Qtr4
4	Name3	300	Qtr3
5	Name2	700	Qtr1
6	Name2	200	Qtr4
7	Name2	700	Qtr1
8	Name3	500	Qtr1
9	Name1	500	Qtr1
10	Name2	1000	Qtr2
11	Name1	800	Qtr4
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIFS(B2:B11,A2:A11,D2,C2:C11,E2)

 

[VbaHell]

This is very crude but is working

I added additional columns for each "Qtr1, 2, etc and then added up each column

[=SUMIFS('Supplier Dashboard'!BB:BB,'Supplier Dashboard'!A:A,V1,'Supplier Dashboard'!BF:BF,D5)+SUMIFS('Supplier Dashboard'!BG:BG,'Supplier Dashboard'!A:A,V1,'Supplier Dashboard'!BK:BK,D5)+SUMIFS('Supplier Dashboard'!BL:BL,'Supplier Dashboard'!A:A,V1,'Supplier Dashboard'!BP:BP,D5)+SUMIFS('Supplier Dashboard'!BQ:BQ,'Supplier Dashboard'!A:A,V1,'Supplier Dashboard'!BV:BV,D5)]

Thanks everyone for your help on this

 

[AlanY]

care to show a small sample of your data here?