Using named references dynamically

[Molecheese]

I have a table of data, with various categories and months, as below. The categories are grouped using named references - GROUP1, GROUP2, GROUP3 - and I am trying to write a formula to show the sum of all GROUP1 items for a particular month as defined by a separate cell. Please ask if you need any more information - any help would be great. Thanks!

 

[Fluff]

What version of Excel are you using?

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’)

Also are Group1 etc actual named ranges in the name manager?

 

[Molecheese]

Thanks details updated - yes, the Group1, Group2 etc are named ranges. I think I found a way to get what I'm after by also naming the columns - MonthJan, MonthFeb for example - and using Sumproduct or something similar, but there is a lot of data so I would like to avoid having to create so many named ranges if I can. Hope that helps - please let me know if you need more

 

[Fluff]

Ok, if you change the Group ranges to cover (for instance) A2:G4 instead of A2:A4 then you can use

Fluff.xlsm
	A	B	C	D	E	F	G	H
1		Jan	Feb	Mar	Apr	May	Jun	Jul
2	A	1	2	3	4	5	6
3	B	7	8	9	10	11	12
4	C	13	14	15	16	17	18
5	D	19	20	21	22	23	24
6	E	25	26	27	28	29	30
7	F	31	32	33	34	35	36
8	G	37	38	39	40	41	42
9	H	43	44	45	46	47	48
10	I	49	50	51	52	53	54
11
12		Group1	Group2	Group3
13	Mar	27	81	135
14	Jun	36	90	144
Master

Cell Formulas
Range	Formula
B13:B14,C14:D14	B13	=SUM(INDEX($B$2:$H$10,,XMATCH($A13,$B$1:$H$1)) INDIRECT(B$12))
C13:D13	C13	=SUM(INDEX($B$2:$H$10,,XMATCH($A13,$B$1:$H$1)) INDIRECT(C12))

 

[Molecheese]

Amazing - that works perfectly. Thank you so much for your help

 

[Fluff]

You're welcome & thanks for the feedback.