CODEWITHSUNDEEP
excelexcel-formula
WaveWalker116
WaveWalker116

Reputation: 43

SUM UNIQUE except...?

Okay, this may get confusing, but I'll do my best to describe it.

I'm trying to calculate the amount of days a task takes to complete. So I have a spreadsheet that lists the tasks, assigns hours, and assigns a person to do that task.

So, say Task 1 has 5 sub-tasks, and each take 8 hours. If I assign Bill to each task, Bill will take 5 days to complete Task 1. That's easy.

Now, if it takes Bill 5 days to complete Task 1, but the deadline is in 4 days, I simply take 1 of the sub-tasks from Bill and assign to Bob, and viola... we now have 40 hours of work being done in 4 days.

I have all this working with the following formula:

=MAX(SUMIFS('Task List'!Q:Q,'Task List'!T:T,UNIQUE('Task List'!T:T),'Task List'!D:D,"<>"&"*Summary"))

The formula looks down column T and finds any UNIQUE names. It then basically puts those hours happening at the same time as the other hours (from column Q) since Bill and Bob can work at the same time. Another way to look at it is: the person with the most tasks assigned to them is controlling the end date, since that's the "longest" everything will take to get done.

Now the question...

There will ALWAYS be a task at the end of each project called "baking". "Baking" will always be in column H. Baking will always be assigned to a UNIQUE person not on the project... so Bill and Bob will be part of the team, but Baking will always be a different Unique person.

So what happens is the above formula is subtracting out the Baking line because it's a unique name.

But I don't want that. The Baking task will always happen after the other tasks are done and can't be done while Bill and Bob are doing their tasks.

So, what I need the formula to say is, "Look down Column T for any unique names and add the person's hours which appears the most, but ignore this if column G has "baking" in it, and always add these hours normally."

If it helps, the first image below should return the full Value of "80" because Bill is doing all the tasks and Baker is baking once Bill is done, so the total time for the project is 80 hours. However, the above formula returns "70", because "Baker" is Unique.

Task List 1

This one SHOULD return 70, because Bob took 10 hours from Bill's plate, but again, Baker happens after all tasks are done (Bill: 40+10+10 + Baker 10):

Task List 2

Upvotes: 0

Views: 67

Answers (2)

P.b
P.b

Reputation: 11468

=SUMPRODUCT(--(E:E<>""),Q:Q)+MAX(SUMIFS(Q:Q,T:T,UNIQUE(T:T),F:F,"<>"&""))+SUMPRODUCT(--(G:G<>""),Q:Q) This would do the trick as well. Looking for the time for gathering ingredients + max time of unique employees + time for baking.

Upvotes: 1

WaveWalker116
WaveWalker116

Reputation: 43

Answered in the other thread...

=MAX(SUMIFS(Q:Q,T:T,UNIQUE(T:T),D:D,"<>"&"*Summary",G:G,"<>Baking"))+SUMIFS(Q:Q,G:G,"Baking")

Upvotes: 0

Related Questions