# SUM of Cell Across Entire Workbook IF another cell also equals a value

#### indiemusicboy

##### New Member
I have a workbook that contains (1) worksheet that will serve as a 'dashboard' or rollup of all other worksheet data in the workbook. The subsequent worksheets are form the same templates that will be reused (added) as net new worksheets in the workbook over time. So the dahsboard will stay the same, all other worksheets are from the same template but filled with different values.

In other words, Dashboard rollup, remaining worksheets are the same criteria about a customer people fill out per customer.

On the Dashboard worksheet, I want to sum up cell D121 on every worksheet in the workbook (not the dashboard) IF cell L3 equals a particular person's name.

I created a "FIRST" and "LAST" worksheet to give me buffers in the following formula but I keep getting Value or SLIP errors

=SUMIFS(First:Last!D121,L3,"Amanda")

D121 = a monetary value
L3 is a list of names where one is selected
"Amanda" is the name selected in this equation

So in the end if it searches all the worksheets and finds "Amanda" in L3, it will then report back a sum of cell D121 on those pages.

Can you help?

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### jasonb75

##### Well-known Member
The First:Last method doesn't work with criteria, you need to list the sheets individually as shown in post 2 here, SUMIF MULTIPLE SHEETS

Also, the criteria range, L3 in your current formula is referring to the dashboard, you will need to apply the sheet names to that as well as the sum range.

#### indiemusicboy

##### New Member
The First:Last method doesn't work with criteria, you need to list the sheets individually as shown in post 2 here, SUMIF MULTIPLE SHEETS

Also, the criteria range, L3 in your current formula is referring to the dashboard, you will need to apply the sheet names to that as well as the sum range.
Thank you for your response! I see that from @Aladin Akyurek which makes sense. But in my situation, I will have multiple sales people creating a new worksheet based on the existing template for any new customer. So new customer, new worksheet from a template.

I'm trying to find a way that on my "Dashboard" it searches an always changing range of worksheets that regardless if someone adds a new worksheet or removes one, it will search it and I won't have to manipulate the formula in the dashboard. Which is why I was using the "First:Last" idea in some places on this dashboard to great success. But I don't think that works when you have multiple criteria?

So...

Constants:
Always D121 for the monetary value of the client
Always L3 for the sales person's name of that client

Not Constant:
The amount of worksheets

#### Fluff

##### MrExcel MVP, Moderator
One option would be to put this formula on each sheet (say in D122)
Excel Formula:
``=IF(L3="Amanda",D121,0)``
then you can use
Excel Formula:
``=SUM(First:Last!D122)``

#### indiemusicboy

##### New Member

One option would be to put this formula on each sheet (say in D122)
Excel Formula:
``=IF(L3="Amanda",D121,0)``
then you can use
Excel Formula:
``=SUM(First:Last!D122)``
I like where that is going, but here is my problem:

L3 is a "List" of all (say 6) sales reps. Amanda is just one of them.

So it sounds like I'm trying to find a way to have the \$ amount from D121 also capture the sales rep's name from L3 on that same worksheet, so it can add up all the like worksheets that meet that criteria to the dashboard cell.

#### Fluff

##### MrExcel MVP, Moderator
Do you have select the rep's name on the dashboard to see their figures, or do you have a cell for each rep?

#### indiemusicboy

##### New Member
Do you have select the rep's name on the dashboard to see their figures, or do you have a cell for each rep?
Cell for each rep. Here is a screen shot of the Dashboard. I'm trying to have it look at all subsequent worksheets and the reps will add these sheets ad hoc so it needs to continue to look at the entire workbook without updating the dashboard equation if avoidable.

Here is an example of the data it is trying to gather; if it has their name in any of the worksheets in this particular cell (L3 list)...

... then it will pull this cell's value to the Dashboard I36 cell for Amanda. Intent is to sum up all the value in D121 across all her spreadsheets with her name in L3.

#### Fluff

##### MrExcel MVP, Moderator
In that case I think that you either do it the way Jason suggested, or you have a cell on each sheet for each rep.
so for sake of argument if the rep is Amanda you add the total to E121 & if it's Ryan you add the total to F121 etc & then use a 3D sum on the dashboard looking at the relevant cell.

Replies
2
Views
73
Replies
2
Views
115
Replies
8
Views
353
Replies
1
Views
64
Replies
3
Views
463

1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

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