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

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,666
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
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.
Screen Shot 2021-03-16 at 8.30.37 PM.png


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

Screen Shot 2021-03-16 at 8.38.51 PM.png


... 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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,122
Messages
5,640,243
Members
417,131
Latest member
Seanr19871

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?

Disable AdBlock

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
Top