Nested Ifs Alternative for Excel 2010

rokew

New Member
Joined
Aug 13, 2019
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
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
CPACAPBCBPCCCDCECFCGCHCICJCKCLCOCP
101-January02-February03-March04-April05-May06-June
207-July08-August09-Setember10-October11-November12-December
3Copy-paste from above:11-November
4
5.DIFFDIFFDIFFDIFFUp to monthUp to monthUp to monthUp to monthUp to monthDIFFDIFF
6Total 2015Total 2016Total 2017Total 2018Total 2019Total 2020FULL YEARFULL YEARFULL YEARFULL YEAR2.0162.0172.0182.0192.020CURRENT MONTHCURRENT MONTH
72017-20162018-20172019-20182020-201911-November11-November11-November11-November11-November2019-20182020-2019
8Type A110.421,31169.471,57169.975,9265.837,8138.822,202,00504,35-104.138,11-27.015,61-38.820,20149.995,62162.916,8562.536,6137878,82,00-24.657,81-37.876,80
test
Cell Formulas
RangeFormula
CH7:CL7CH7=+$CK$3
CC8CC8=SUM(BQ8:CB8)
CD8CD8=AP8-AC8
CE8CE8=+BC8-AP8
CF8CF8=+BP8-BC8
CG8CG8=+CC8-BP8
CH8CH8=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)))))))))))))
CI8CI8=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)))))))))))))
CJ8CJ8=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)))))))))))))
CK8CK8=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)))))))))))))
CL8CL8=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:CP8CO8=+CK8-CJ8
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
Incredibly short, a great improvement.
Thanks so much for helping me fix this formula!
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top