# Nesting 12 if functions, How?

#### Amy from Charlotte

##### New Member
I am trying to nest 12 if functions with no success, I have read several other posts regaring this and have tried some of the suggested formulas but still can't get it to work right, please help!

Here is the formula:
=(IF(\$AF\$74=1,B78,"")&IF(\$AF\$74=2,SUM(B78:C78),"")&IF(\$AF\$74=3,SUM(B78:D78),"")&IF(\$AF\$74=4,SUM(B78:E78),"")&IF(\$AF\$74=5,SUM(B78:F78),"")&IF(\$AF\$74=6,SUM(B78:G78),"")&IF(\$AF\$74=7,SUM(B78:H78),"")&IF(\$AF\$74=8,SUM(B78:I78),"")&IF(\$AF\$74=9,SUM(B78:J78),"")&IF(\$AF\$74=10,SUM(B78:K78,"")))

So the idea is that if cell equals 1 then return a value, if cell equals 2 then sum 2 values and return, if cell equals 3 then sum 3 values and return. When I enter the formula above I get a #VALUE error. What am I doing wrong?

Thanks!!!

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### phantom1975

##### MrExcel MVP
You are better off setting up a table with your data and creating a LOOKUP formula instead.

#### KenWright

##### Active Member
=SUM(OFFSET(B78,,,AF74))

As Oaktree pointed out I had missed a comma - Grrrrrr - Serves me right for just typing it and not trying it.

=SUM(OFFSET(B78,,,,AF74))

#### Oaktree

##### MrExcel MVP
Try = sum(B78ffset(B78,0,\$af\$74-1))

edit: I like Ken's more, but I think his should have 4 commas instead of 3

=SUM(OFFSET(B78,,,,AF74))

##### Well-known Member
The reason it isn't working, is that the & operator makes it text not number, even though it looks like a number.

#### Amy from Charlotte

##### New Member
Thank you Oaktree and Ken, it worked, ya'll are incredible, I should have posted this 3 hours ago!!! THANK YOU!

#### KenWright

##### Active Member
LOL - You're welcome

( And thanks Oaktree )

Replies
1
Views
198
Replies
1
Views
351
Replies
2
Views
719
Replies
6
Views
271
Replies
4
Views
616

1,181,993
Messages
5,933,151
Members
436,881
Latest member
CMScons

### 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.

### Which adblocker are you using?

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

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