Nested Ifs Alternative for Excel 2010

[rokew]

Hello,

I’m using Excel 2010 ( no comments!) and I have an extensive monthly report based on dynamic table and nested IFs but I wonder if there is a better and simpler solution.

I have read some questions in the forum and it seems difficult to achieve the same results with a more elegant formula. I haven’t found an alternative in the previous answers suitable to my case. Maybe I’m missing out?

Each of my dynamic tables includes the monthly data of 4 years (13 columns per year: 12 months+total)

In order to compare the evolution in similar periods, I must show the accumulated total up to certain month of every year.

To do so I use additional columns out of the table. According to my nested IFs formula, if I choose the cell containing the text “February” , the destination cell sums columns from January to February, if I choose “November”, the cell sums columns from January to November….

The result is a very long formula:

=IF(CH$7=Q$7,SUM($Q8:$Q8),IF(CH$7=R$7,SUM($Q8:$R8),IF(CH$7=S$7,SUM($Q8:$S8),IF(CH$7=T$7,SUM($Q8:$T8),IF(CH$7=U$7,SUM($Q8:$U8),IF(CH$7=V$7,SUM($Q8:$V8),IF(CH$7=W$7,SUM($Q8:$W8),IF(CH$7=X$7,SUM($Q8:$X8),IF(CH$7=Y$7,SUM($Q8:$Y8),IF(CH$7=Z$7,SUM($Q8:$Z8),IF(CH$7=AA$7,SUM($Q8:$AA8),IF(CH$7=AB$7,SUM($Q8:$AB8)))))))))))))


Can I use a simple strategy in my old Excel 2010?

Thanks in advance

MR EXCEL_NESTED IF.xlsx
	C													P													AC													AP													BC													BP													CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL			CO	CP
1																																																																																	01-January	02-February	03-March	04-April	05-May	06-June
2																																																																																	07-July	08-August	09-Setember	10-October	11-November	12-December
3																																																																																						Copy-paste from above:	11-November
4
5	.																																																																															DIFF	DIFF	DIFF	DIFF	Up to month	Up to month	Up to month	Up to month	Up to month			DIFF	DIFF
6														Total 2015													Total 2016													Total 2017													Total 2018													Total 2019													Total 2020	FULL YEAR	FULL YEAR	FULL YEAR	FULL YEAR	2.016	2.017	2.018	2.019	2.020			CURRENT MONTH	CURRENT MONTH
7																																																																																2017-2016	2018-2017	2019-2018	2020-2019	11-November	11-November	11-November	11-November	11-November			2019-2018	2020-2019
8	Type A													110.421,31													169.471,57													169.975,92													65.837,81													38.822,20													2,00	504,35	-104.138,11	-27.015,61	-38.820,20	149.995,62	162.916,85	62.536,61	37878,8	2,00			-24.657,81	-37.876,80
test

Cell Formulas
Range	Formula
CH7:CL7	CH7	=+$CK$3
CC8	CC8	=SUM(BQ8:CB8)
CD8	CD8	=AP8-AC8
CE8	CE8	=+BC8-AP8
CF8	CF8	=+BP8-BC8
CG8	CG8	=+CC8-BP8
CH8	CH8	=IF(CH$7=Q$7,SUM($Q8:$Q8),IF(CH$7=R$7,SUM($Q8:$R8),IF(CH$7=S$7,SUM($Q8:$S8),IF(CH$7=T$7,SUM($Q8:$T8),IF(CH$7=U$7,SUM($Q8:$U8),IF(CH$7=V$7,SUM($Q8:$V8),IF(CH$7=W$7,SUM($Q8:$W8),IF(CH$7=X$7,SUM($Q8:$X8),IF(CH$7=Y$7,SUM($Q8:$Y8),IF(CH$7=Z$7,SUM($Q8:$Z8),IF(CH$7=AA$7,SUM($Q8:$AA8),IF(CH$7=AB$7,SUM($Q8:$AB8)))))))))))))
CI8	CI8	=IF(CI$7=AD$7,SUM($AD8:$AD8),IF(CI$7=AE$7,SUM($AD8:$AE8),IF(CI$7=AF$7,SUM($AD8:$AF8),IF(CI$7=AG$7,SUM($AD8:$AG8),IF(CI$7=AH$7,SUM($AD8:$AH8),IF(CI$7=AI$7,SUM($AD8:$AI8),IF(CI$7=AJ$7,SUM($AD8:$AJ8),IF(CI$7=AK$7,SUM($AD8:$AK8),IF(CI$7=AL$7,SUM($AD8:$AL8),IF(CI$7=AM$7,SUM($AD8:$AM8),IF(CI$7=AN$7,SUM($AD8:$AN8),IF(CI$7=AO$7,SUM($AD8:$AO8)))))))))))))
CJ8	CJ8	=IF(CJ$7=AQ$7,SUM($AQ8:$AQ8),IF(CJ$7=AR$7,SUM($AQ8:$AR8),IF(CJ$7=AS$7,SUM($AQ8:$AS8),IF(CJ$7=AT$7,SUM($AQ8:$AT8),IF(CJ$7=AU$7,SUM($AQ8:$AU8),IF(CJ$7=AV$7,SUM($AQ8:$AV8),IF(CJ$7=AW$7,SUM($AQ8:$AW8),IF(CJ$7=AX$7,SUM($AQ8:$AX8),IF(CJ$7=AY$7,SUM($AQ8:$AY8),IF(CJ$7=AZ$7,SUM($AQ8:$AZ8),IF(CJ$7=BA$7,SUM($AQ8:$BA8),IF(CJ$7=BB$7,SUM($AQ8:$BB8)))))))))))))
CK8	CK8	=IF(CK$7=BD$7,SUM($BD8:$BD8),IF(CK$7=BE$7,SUM($BD8:$BE8),IF(CK$7=BF$7,SUM($BD8:$BF8),IF(CK$7=BG$7,SUM($BD8:$BG8),IF(CK$7=BH$7,SUM($BD8:$BH8),IF(CK$7=BI$7,SUM($BD8:$BI8),IF(CK$7=BJ$7,SUM($BD8:$BJ8),IF(CK$7=BK$7,SUM($BD8:$BK8),IF(CK$7=BL$7,SUM($BD8:$BL8),IF(CK$7=BM$7,SUM($BD8:$BM8),IF(CK$7=BN$7,SUM($BD8:$BN8),IF(CK$7=BO$7,SUM($BD8:$BO8)))))))))))))
CL8	CL8	=IF(CL$7=BE$7,SUM($BQ8:$BQ8),IF(CL$7=BF$7,SUM($BQ8:$BR8),IF(CL$7=BG$7,SUM($BQ8:$BS8),IF(CL$7=BH$7,SUM($BQ8:$BT8),IF(CL$7=BI$7,SUM($BQ8:$BU8),IF(CL$7=BJ$7,SUM($BQ8:$BV8),IF(CL$7=BK$7,SUM($BQ8:$BW8),IF(CL$7=BL$7,SUM($BQ8:$BX8),IF(CL$7=BM$7,SUM($BQ8:$BY8),IF(CL$7=BN$7,SUM($BQ8:$BZ8),IF(CL$7=BO$7,SUM($BQ8:$CA8),IF(CL$7=BP$7,SUM($BQ8:$CB8)))))))))))))
CO8:CP8	CO8	=+CK8-CJ8

 

[jasonb75]

Unless I'm missing something

=SUM($Q8:INDEX($Q8:$AB8,MATCH(CH$7,$Q$7:$AB$7,0)))

Not sure how far back this method will work, I'm sure I used it in excel 2003

 

[rokew]

Incredibly short, a great improvement.
Thanks so much for helping me fix this formula!