How to get drop-down selection to change range reference in a sumifs formula?

[excelnoobnoob]

Hi all,
I'm having trouble working this out...

I'm using this formula:

=SUMIFS('HighLights Log'!Q:Q,'HighLights Log'!$F:$F,"BULLISH",'HighLights Log'!$C:$C,TODAY())

At the moment I guess you would call sum_range 'static'. I would like the sum_range to be variable based on the value in a cell.

So if cell F3=1, sum_range is M:M, if cell F3=2, sum_range is N:N, if cell F3=5, sum_range=O:O
Could anybody point me in the right direction to solve this pelase?

Thanks!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would try to avoid whole column references. Does this simple demonstration do the sort of thing that you want?

22 05 21.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1								7	3	4	7					a
2								9	3	7	3					b
3	2	21						7	5	6	7					a
4								6	9	2	6					b
5								8	4	9	2					a
6								5	5	6	6					b
7								5	9	1	7					a
8								2	5	5	5					b
SUMIFS

Cell Formulas
Range	Formula
G3	G3	=SUMIFS(INDEX(M1:P8,0,F3),U1:U8,"a")