# Trying to SUM two amounts in a formula that are calculated in the same formula

#### leopardhawk

##### Well-known Member
Hello forum friends, I have a complex formula that works well for what I am trying to do but I would like to be able to SUM two amounts that are calculated within the same formula. I have pulled out a small section of the formula below:

=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"\$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"\$#,##0.00")&" for a total of \$750.00.")

What I end up with is...

"Their annual PRB at age 67 is \$391.29 + \$382.38 for a total of \$750.00."

What I would like to do is replace the \$750.00 with the SUM of the first two dollar amounts (which is \$773.67). The first two dollar amounts are also dynamic, depending on what is in the power query (CPP_OAS).

Appreciate any ideas or suggestions!

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Candyman8019

##### Board Regular
try the following:

=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"\$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"\$#,##0.00")&" for a total of \$"& (INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"\$#,##0.00") + INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"\$#,##0.00")&".")

#### leopardhawk

##### Well-known Member
@Candyman8019 thanks for the idea. I copied your formula into a blank cell to test it and while I didn't receive an error, all that is displayed in the cell is the formula, not the result.

#### Candyman8019

##### Board Regular
Oh...I failed to removed the text format information in the previous formula...try this one:

=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"\$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"\$#,##0.00")&" for a total of \$"& (INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084)) + INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084)&".")

#### leopardhawk

##### Well-known Member

@Candyman8019 same result. I appreciate your efforts though! I was thinking the solution would be easier.

#### Candyman8019

##### Board Regular
Rather than just paste it into a cell, paste it into the edit bar at the top of the screen. Sometimes when pasting this sort of thing it takes it as text for some reason.

#### leopardhawk

##### Well-known Member

@Candyman8019 wow, now I am getting an error ("There's a problem with this formula."), you know, the one that asks you if you're actually trying to type a formula? I can see that you have removed the TEXT portion in your suggestion but for some reason, it doesn't like it.

#### Candyman8019

##### Board Regular
You may need to play around with the formula depending on data formats, you might need to add the TEXT clauses back in. Essentially all we're doing is taking the parts of the formula that originally displayed the two values as text and add them together at the end.

Maybe give something like this a try:
=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"\$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"\$#,##0.00")&" for a total of "& text((INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084)) + INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084),"\$#,##0.00")&".")

Here I wrapped the calculation in a TEXT clause.

#### leopardhawk

##### Well-known Member
@Candyman8019 I certainly will play with it a bit, thanks for your efforts. Your latest formula gives a different error ("You've entered too few arguments for this function.")

#### Candyman8019

##### Board Regular
I’m not in front of my computer at the moment but I feel like that has to do with bracket placement.

Replies
12
Views
397
Replies
3
Views
81
Replies
3
Views
161
Replies
18
Views
246
Replies
7
Views
91

1,130,174
Messages
5,640,593
Members
417,154
Latest member
gm_jagath

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