SUMIFS - multiple criteria (one dynamic) across several columns of data

[Chelseablue]

Hi,

I am trying to create a SUMIFS formula to calculate predicted sales of widgets by salesman, by time period and by widget type. The results are then displayed in a summary table. The formula works fine for a single column but when expanded to include the full range of widgets - it falls over.

I want the Widget type to be selected from a drop down list in cell A1 and as the type is changed the summary table will display the results.

The formula I am trying to use is: =SUMIFS(C3:F12,C2:F2,A1,A3:A12,B20,B3:B12,A21) but returns a #VALUE! error

Excel 2010
	A	B	C	D	E	F
1	Widget A
2			Widget A	Widget B	Widget C	Widget D
3	Fred	2016 Q1	£5,000.00
4	George	2016 Q1	£1,000.00	£2,000.00	£500.00	£500.00
5	Fred	2016 Q3	£500.00
6	Fred	2016 Q4	£250.00
7	Fred	2017 Q3	£1,000.00	£1,000.00	£750.00	£250.00
8	George	2017 Q4	£1,000.00
9	Andrew	2016 Q4		£2,000.00	£500.00
10	Andrew	2016 Q2
11	Andrew	2016 Q3	£1,000.00
12	Andrew	2016 Q2		£5,000.00	£5,000.00
Sheet1

Excel 2010
	A	B	C	D
19
20		Andrew	Fred	George
21	2016 Q1	#VALUE!
22	2016 Q2
23	2016 Q3
24	2016 Q4
25	2017 Q1
26	2017 Q2
27	2017 Q3+
Sheet1

I have been looking at this formula for several days now and must confess to going around in circles - any help that you can provide would very gratefully received.

Hopefully I have given sufficient information.

regards

 

[Jonmo1]

Try

=SUMIFS(INDEX(C3:F12,0,MATCH(A1,C2:F2,0)),A3:A12,B20,B3:B12,A21)

 

[Chelseablue]

Fantastic !

That has worked a treat, thank you so much for the help and saving my sanity

 

[Jonmo1]

You're welcome.