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

#### leopardhawk

##### Well-known Member
Thanks Candyman8019. Maybe someone else can step in on this. I feel like the solution is simple but just out of my reach. I welcome any and all ideas/suggestions.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### leopardhawk

##### Well-known Member
Okay, I think I'm on the right track but not quite there yet. I was able to get the formula to return the correct amount by adding in a couple of missing brackets and a missing quote but without the TEXT formatting, the sentence looks like this...

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

This is what I have so far in the formula...

=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)&"."

When I tried adding that bit of formatting (,"\$#,##0.00") to the end of the formula, I get an error (There is a problem with this formula). Can anyone help me fix this? Thanks!

#### Candyman8019

##### Board Regular
Give this a try. I added the ROUND function to the calculation piece:

=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 \$"&round((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),2)&"."

#### leopardhawk

##### Well-known Member
I'm getting the error "You've entered too few arguments for this function."

#### Candyman8019

##### Board Regular

We're so close...I think we just had a close bracket in the wrong spot.

=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 \$"&round((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,2)&".")

#### leopardhawk

##### Well-known Member
@Candyman8019 YAY!!!! I'm so pleased, that works great. Thanks very much for reaching out to help!

My pleasure.

#### leopardhawk

##### Well-known Member
@Candyman8019 Although your solution solved my initial problem, I am trying to expand on what I learned from you as I improve my formula. So, what I am trying to do is add another line of text using the same logic but I run into an error when I try to ROUND the last amount. My plan is to use the dollar amount from the end of each line and add it to a calculated amount on the next line. It's probably a missing bracket or maybe one in the wrong place?? Anyway, here is the formula, and below that is the result I am getting. As you can see, the last amount is to 10 decimal places instead of 2 which is what I need it to be (2).

Formula:
=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 \$"&ROUND((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,2)&".
", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 68 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"\$#,##0.00")&" + \$"&ROUND((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,2)&" for a total of \$"&ROUND((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,2)+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084)&".")

Result:
Their annual PRB at age 67 is \$391.29 + \$382.38 for a total of \$773.67.
Their annual PRB at age 68 is \$400.39 + \$773.67 for a total of \$1174.0600100436.

Again, thanks for your help, I really appreciate it!

#### Candyman8019

##### Board Regular
I’m not at my computer at the moment but taking a quick look seems like you might need an open bracket before:
0.025*12*1.084,2)+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084)&".")

#### leopardhawk

##### Well-known Member
Doing that gives an error. The formula works as it is without errors but I am trying to ROUND down the last amount so that it will display as currency. That's when I get errors, when I try and ROUND it down. Thanks again for helping with this...

Replies
12
Views
368
Replies
3
Views
78
Replies
3
Views
157
Replies
18
Views
243
Replies
7
Views
88

1,129,299
Messages
5,635,387
Members
416,856
Latest member
silentir

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