Alternative to multiple nested IF statements to SUM certain values

**JTR616** · 09-24-2013, 09:17 AM

This is going to be difficult to explain and may take a few post. I'm attempting to work on an income statement analysis and have the 2012 and 2013 income statements in excel and would like to do a month by month analysis to calculate revenue growth and other metrics. I would like to do so having a drop down menu on the top of the worksheet where you can select the month and the fields will automatically populate by retrieving the information. I have been able to do this by nesting 13 IF statements together and it does achieve my desired result. In column B I have it calculating the current month and in column C I have it sum the numbers for that account for the year to date. The table array is several columns over the right. Below are my formulas

Month-=IF($E$1=$X$1,X17,IF($E$1=$Y$1,Y17,IF($E$1=$Z$1,Z17,IF($E$1=$AA$1,AA17,IF($E$1=$AB$1,AB17,IF($E$1=$AC$1,AC17,IF($E$1=$AD$1,AD17,IF($E$1=$AE$1,AE17,IF($E$1=$AF$1,AF17,IF($E$1=$AG$1,AG17,IF($E$1=$AH$1,AH17,IF($E$1=$AI$1,AI17,IF($E$1=$AJ$1,AJ17,"N/A")))))))))))))

YTD-=IF($E$1=$X$1,X17,IF($E$1=$Y$1,SUM(X17:Y17),IF($E$1=$Z$1,SUM(X17:Z17),IF($E$1=$AA$1,SUM(X17:AA17),IF($E$1=$AB$1,SUM(X17:AB17),IF($E$1=$AC$1,SUM(X17:AC17),IF($E$1=$AD$1,SUM(X17:AD17),IF($E$1=$AE$1,SUM(X17:AE17),IF($E$1=$AF$1,SUM(X17:AF17),IF($E$1=$AG$1,SUM(X17:AG17),IF($E$1=$AH$1,SUM(X17:AH17),IF($E$1=$AI$1,SUM(X17:AI17),"N/A"))))))))))))

My question is can anyone think of a more efficient way to do this, my current formulas work but if I were to make a mistake in imputing the formula I was almost certainly not be able to see my mistake.

Any suggestions would be appreciated.

**Pete_UK** · 09-24-2013, 09:22 AM

The first formula can be achieved with a lookup function, rather than multiple-IFs. The other one can also be simplified, but it will be easier to see how if you attach your workbook - the FAQ describes how to.

While you are reading that, you should also read the Forum Rules on appropriate naming of threads, and change yours to something more meaningful.

Hope this helps.

Pete

**Jonmo1** · 09-24-2013, 09:51 AM

Try these

=IFERROR(HLOOKUP($E$1,$X$1:$AJ$17,17,FALSE),"N/A")

=IFERROR(SUM($X$17:INDEX($X$17:$AJ$17,MATCH($E$1,$X$1:$AJ$1,0))),"N/A")

**JTR616** · 09-24-2013, 12:14 PM

Example IF Problem.xlsx

For confidentiality reasons I can't attach my actual spreadsheet but I did quickly make up a similar one with the same IF statement I used to calculate the YTD revenue. Any potential help on making this formula easier to interpret would be greatly appreciated. I incorporated the lookup function you recommended for the monthly revenue and it works great so thanks for the help!