Can I add HAS formula to this existing formula?

Nathan White

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"

bisaacs

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?

Nathan White

https://community.smartsheet.com/discussion/comment/431064#Comment_431064

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?

bisaacs

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

Nathan White

https://community.smartsheet.com/discussion/comment/431084#Comment_431084

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)