# Summing with variables

#### DPO

##### Board Regular
Hello. I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'. I hope this makes sense. Thanks in advance!

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### ravishankar

##### Well-known Member
Hi
Paste the following codes in the macro window ( alt F11)

Code:
Sub Macro1()
Range("A1").Select
Selection.ListNames
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
b = Cells(a, 1)
Cells(x + 1, 1) = Cells(x + 1, 1) & Cells(a, 1) & ","
c = Cells(x + 1, 1)
d = Left(c, Len(c) - 1)
Cells(x + 3, 1) = "=Sum(" & d & ")"
Next a
End Sub
run the macro. It will list all salespersons in col A and their total underneath it.
Ravi

#### DPO

##### Board Regular
Hi Ravishankar, Thanks for the reply, however, I was hoping to do this via a formula and not a macro. Any thoughts?

#### DPO

##### Board Regular
As a follow-up, here is what I have tried so far (but w/o any luck):

=IF(ISBLANK(E\$1),"",SUM(INDIRECT("SalesPerson"&"*"&"Total"))/INDIRECT("SalesPerson"&E\$1&"Total"))

Where the value of E1 (F1, G1, etc.) will be the names of each potential Sales person. This formula should sum all of the total sales (already summed on their individual tabs) of each respective sales person and then divide by the total for the person represented in E1, F1, G1, etc. to provide a % of total sales per salesperson.

#### ravishankar

##### Well-known Member
Hi
in any cell enter
=SUM(salesperson1total,salesperson2total,salesperson3total,....) will give you the total of the range mentioned.essentially that is what my macro does.
Ravi

#### DPO

##### Board Regular
Hi. Thanks again for the reply. I understand that I could 'hard code' in the defined names for each iteration of 'SalesPerson(x)Total', however, the number (x) of SalesPersons will continue to change, therefore, I would like to make the formula dynamic in that for any defined name with a naming convention = to 'SalesPerson(x)Total', the formula should total the values of those fields. Does that make sense?

#### ravishankar

##### Well-known Member
HI
I am confused. if I say salesperson(10)total: does it mean 10th salesman - john's total or total of 10 salesmen? Which cells are these info. stored
Ravi

#### DPO

##### Board Regular
Hi. Currently, for the sake of the example, I have 3 salesmen:

Sheet 2, Cell A1 is a def. name (SalesPerson01Total) which sums A1:A500

Sheet 3 Cell A1 is a def. name (SalesPerson02otal) which sums A1:A500

Sheet 4 Cell A1 is a def. name (SalesPerson03otal) which sums A1:A500

On Sheet 1, I want my formulas to reside in cells E2, F2, and G2 with cells E1, F1, G1 holding the heading 01, 02, etc. I want the formula to be able to accomodate a new tab (Sheets 5, 6, etc.) with the same format/defined name as Sheets 2,3,4 and just need to add a new column heading on Sheet 1 and copy the formula over. I hope that makes my task more clear, please let me know if you need more info.

#### DPO

##### Board Regular
Hi Ravishankar, Just checking in to see if my last reply helped to clear things up and if you have any thoughts on a solution for my formula? Any help is greatly appreciated. Thanks, David

#### DPO

##### Board Regular
Hi all, Is anyone able to help me with this request below? I don't want to post a new thread, but I'm concerned that no one is responding because Ravishankar has been helping me so far. Unfortunately, I have not heard back from him and I have a deadline, so I need to either figure this out in a short time frame or go a completely different (less dynamic/advanced) route than what I'm shooting for. Help....please!

Replies
2
Views
307
Replies
2
Views
149
Replies
3
Views
141
Replies
1
Views
265
Replies
0
Views
187

1,191,703
Messages
5,988,179
Members
440,136
Latest member
dandanfielding

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

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