Finding Letter through Formula

[Pestomania]

Hi all,

I have a table that I need to sum up the years. I have done it simply through formulas like you see but that is not going to last much longer as I now have 50 rows and 20 years which makes for a lot of formulas.

Is there a way to automate this process? 2020 starts in column T.

Preference would be something that would be like:

=SUM(H2&":"&INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN((H2))+11, 4), 1, "")&"2"))

Unfortunately, this doesn't return what I hoped for in columns AQ:AS. I would even go as far as just "identify what row the formula is in" so that there is no concern with that as well.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25		2023 Total	2024 Total	2025 Total		2023 Total	2024 Total	2025 Total
2	Program 1	13	11	32	2	41	3	30	49	26	23	8	20	3	36	10	42	10	13	4	13	7	14	14	38	7	20	41	5	30	34	42	25	43	44	10	6		258	204	307		0.555556	0.460417	1.358333
3	Program 2	6	17	44	26	37	24	35	20	22	9	18	26	20	14	38	40	20	38	17	40	14	19	26	33	18	35	41	13	33	35	32	40	13	46	36	20		284	319	362		0.263889	0.710417	1.858333
Sheet1

Cell Formulas
Range	Formula
AM2:AM3	AM2	=SUM(B2:M2)
AN2:AN3	AN2	=SUM(N2:Y2)
AO2:AO3	AO2	=SUM(Z2:AK2)
AQ2:AS3	AQ2	=SUM(B2&":"&INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN((B2))+11, 4), 1, "")&"2"))

 

[Scott Huish]

Column T says Jul-24, which looks like it should be 2024. How is this 2020?

I don't know why you are using INDIRECT anyway. Why is that the preference?

Make your column headers in AQ2:AS2 just the Year so that it can be referenced by the formula. Format these cells as 0 "Totals"
=SUMPRODUCT($B2:$AK2,--(YEAR($B$1:$AK$1)=AQ$1))

Copy across and down.

year totals.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25		2023 Total	2024 Total	2025 Total		2023 Totals	2024 Totals	2025 Totals
2	Program 1	13	11	32	2	41	3	30	49	26	23	8	20	3	36	10	42	10	13	4	13	7	14	14	38	7	20	41	5	30	34	42	25	43	44	10	6		258	204	307		258	204	307
3	Program 2	6	17	44	26	37	24	35	20	22	9	18	26	20	14	38	40	20	38	17	40	14	19	26	33	18	35	41	13	33	35	32	40	13	46	36	20		284	319	362		284	319	362
Sheet1

Cell Formulas
Range	Formula
AM2:AM3	AM2	=SUM(B2:M2)
AN2:AN3	AN2	=SUM(N2:Y2)
AO2:AO3	AO2	=SUM(Z2:AK2)
AQ2:AQ3,AR3:AS3	AQ2	=SUMPRODUCT($B2:$AK2,--(YEAR($B$1:$AK$1)=AQ$1))
AR2:AS2	AR2	=SUMPRODUCT($B2:$AK2,--(YEAR($B1:$AK1)=AR$1))

 

[Pestomania]

Column T says Jul-24, which looks like it should be 2024. How is this 2020?

I don't know why you are using INDIRECT anyway. Why is that the preference?

Make your column headers in AQ2:AS2 just the Year so that it can be referenced by the formula. Format these cells as 0 "Totals"
=SUMPRODUCT($B2:$AK2,--(YEAR($B$1:$AK$1)=AQ$1))

Copy across and down.

year totals.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25		2023 Total	2024 Total	2025 Total		2023 Totals	2024 Totals	2025 Totals
2	Program 1	13	11	32	2	41	3	30	49	26	23	8	20	3	36	10	42	10	13	4	13	7	14	14	38	7	20	41	5	30	34	42	25	43	44	10	6		258	204	307		258	204	307
3	Program 2	6	17	44	26	37	24	35	20	22	9	18	26	20	14	38	40	20	38	17	40	14	19	26	33	18	35	41	13	33	35	32	40	13	46	36	20		284	319	362		284	319	362
Sheet1

Cell Formulas
Range	Formula
AM2:AM3	AM2	=SUM(B2:M2)
AN2:AN3	AN2	=SUM(N2:Y2)
AO2:AO3	AO2	=SUM(Z2:AK2)
AQ2:AQ3,AR3:AS3	AQ2	=SUMPRODUCT($B2:$AK2,--(YEAR($B$1:$AK$1)=AQ$1))
AR2:AS2	AR2	=SUMPRODUCT($B2:$AK2,--(YEAR($B1:$AK1)=AR$1))

This worked amazingly! I had used the indirect for another formula and thought it was the best one to use, it apparently wasn't. I also didn't clarify correctly on the 2020, but this formula worked perfectly. Thank you!