Sum based on drop downs across multiple sheets

I have multiple sheets that all lead to a "Totals" sheet on the first sheet.

This project is based on caseloads in a school system. Each "sheet" is its own school in the system. I have a drop-down on each sheet for the speech teacher for that school, as well as total hours of service time needed at each school/week.

I need to figure out how to show totals for each clinician on the "totals" sheet based on the dropdown selected (teacher's name), and total hours of service time for each school. I cannot wrap my head around this. I thought it was a SUMIF function (Sum the hours IF the dropdown says Mary will be the clinician), but can't get it to work across sheets and with the dropdown.

screenshot 1.jpg

Find more posts tagged with

Sheets

Formulas

Comments

Andrée Starå

Hi Lauren,

There are a few ways to structure a solution.

Sum everything per Clinician on the separate school's sheet and then use cell links to a Master Metric Sheet and total everything there.
Use cross-sheet formulas to collect everything on the Master Metric Sheet and that will involve creating all the needed ranges needed (2 per school)

Try something along the lines of.

=SUMIF({Clinician range}; [Clinician Select]@row; {School Range})

The same version but with the below changes for your and others convenience.

=SUMIF({Clinician range}, [Clinician Select]@row, {School Range})

Clinician range = Column with Clinician on the specific school's sheet

Clinician Select = Dropdown with all the different Clinicians on the Master Sheet

School Range = Column with hours on the specific school's sheet

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Let me know if I can help in any way!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

laurenlouise

Thanks Andree for your help so far! Before your response, I attempted to work around it another way. I'm still stuck but feel that a solution to doing it this way is probably easier.. Screenshot attached, but, I added a line for hours and clinician at each school to my front totals page and linked the dropdowns under the projected clinician column (so, it will change if we change the dropdown on a specific school).

SO, now I'm wanting to get totals in E1-E4.. I'm hoping now that the totals are all on this sheet, I won't need to mess around with dropdowns/multiple sheets in the formula. When I say it out loud I want the function in E1, for example, to be: Sum of B7-B25 IF "MBS Projected Hours" in C7-C25.. But I can't wrap my head around that formula.. Any ideas?

Sum question.jpg

Andrée Starå

Happy to help!

Can you share the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic day!

Best,

Andrée

Shaine Greenwood

Hello laurenlouise et all,

From your screenshots, it appears that you're building formulas in Excel and not Smartsheet.

Please keep in mind the following:

This community is focused around discussion and assistance with the Smartsheet application as opposed to Microsoft Excel.
Although both applications share similarities in formulas, function syntax and behavior may different between Smartsheet and Excel. It's possible that you may see unexpected results if you're building functions in Excel and then pasting them into a sheet in Smartsheet.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…