Nested Ifs Alternative for Excel 2010

R

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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

V
  • Solved
Average Function
Replies
8
Views
381
Peter_SSs
Peter_SSs
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
K
  • Question Question
Consolidate data from individual months by summing 2, 3 or more months together
Replies
0
Views
317
Kaleo
K
A
  • Solved
Display results in a userform label
Replies
4
Views
477
Av8tordude
A
P
  • Solved
Keep W-L of card game played over time
Replies
6
Views
724
Preacherman771
P

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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