Can I add HAS formula to this existing formula?
I have a formula that pulls hours from one sheet if the department has been selected in a dropdown but the problem is that it has to be a single dropdown and we have multiple departments that could be working on the same project. I would like to find a formula that can pull the hours from the week but from a multi select dropdown so we don't have to have a separate line item for each department that works on a job.
(I only have about 4 months of spreadsheet making knowledge and I will be the SME at my company so any other advice is gladly welcome)
Here is the formula for one of the departments.
=SUM(SUMIFS({Dates1}, {Department}, "Tailoring", {Status}, "WIP"), SUMIFS({Dates1}, {Department}, "Tailoring", {Status}, "Offsite work")) * 1.25
Dates1 = hours required for work during the first week of the year. pulled from "Master Project Tracker"
Department = the single select dropdown list that i would like to be able to make a multi select from "Master Project Tracker"
Status = only pull the hours for the job if it has "WIP" or "Offsite work"
Answers
-
Could you use an IF/AND/OR statement instead, where if it's a WIP AND it's ? Something like:
=IF(AND({Status} = "WIP", OR({Department} = "Tailoring", {Department} = "Department2")), SUM(cell range), "")Would something like that work?
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
I forgot to mentions, I have to take the total hours for the given week and multiply it by 1.25. but only do all of this if the department has hours in the Date Column and the status is "WIP" or "Offsite Work". Did that formula take that into account?
-
@Nathan White Ah, I misunderstood!
Then you could probably do something like:
=IF(AND(OR({Status} = "WIP", {Status} = "Offsite Work"), OR({Department} = "Tailoring", {Department} = "Department2"), [Date Column] >=1), SUM(cell range)*1.25, "")That might get you closer that what you're looking for.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
This is the formula that works.... Just wanted to let you know
=(SUMIFS({Dates1}, {Department}, HAS(@cell, "Engineering"), {Status}, OR(@cell = "WIP", @cell = "Offsite Work")) * 1.25)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 443 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 129 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
