Formula: Lookup and sum duration of specific tasks from Case ID No.

Dear Forum Users,

I am developing a dashboard that is required to lookup a Job ID Number from a list of tasks and report the total duration of a specific task that has been summed in a column over a period.

For example, per the attached sheet:

Sheet A (Chronology) Four Columns:
(1) Case ID (Drop list) – (2) Category (Drop list) – (3) Description – (4) From – (5) Network Days

Sheet B (Dashboard):

KEY Action> Needs to report the total networking days for a project listed in (1) Case ID and tasks from (2) Category (Drop list)

My question, what formula would I need to give me the result of networking days by Case ID and Category (Drop list)?

Any help or direction would be greatly appreciated.

A guess.... expected answers are better than a pile of non-working formulae...

=SUMIFS(Chronology!F:F,Chronology!A:A,'Dash Board'!$B$1,Chronology!B:B,'Dash Board'!B4)

Edit: attachment removed for data protection reasons. Anonymised version available below.

Before going further... is it giving you the expected resuts, or did I guess wrongly?

I am not sure what you mean by your new requirement

Can they be multiple and independent at the same time?

Are you wanting a SUM of multiple IDs... or to be able to generate reports one at a time.

Confused...

OK. In that case, would it be a good idea to have B1 populated from a drop-down box containing a list of projects, derived from the first sheet?


Secondly, since you have O365, why are you still using the prehistoric .xls file format (that went out in 2007....)

Your DD is being populated manually at the moment. It can be done using a formula. since the data is in a table, in "Do Not Delete"it can't be done with O365's nice shiny UNIQUE function, but there are alternatives.

I created a new Named Range, and changed an existing one.

Cases:
=Chronology!$A$2:INDEX(Chronology!$A:$A,AGGREGATE(14,6,MATCH({1E+100,"Zzzz"},Chronology!$A:$A),1))

this is based on a bright idea from Bo_Ry... a forum regular. It adjusts automatically to the length of chronology column A. No need to adjust it.

In DnD, D2:
=UNIQUE(Cases,FALSE)

again, no need to adjust, ever.

Case_Id for the DD in B1:
='DO NOT DELETE'!$D$2:INDEX('DO NOT DELETE'!$D:$D,AGGREGATE(14,6,MATCH({1E+100,"Zzzz"},'DO NOT DELETE'!$D:$D),1))

The DD will always contain a list of unique projects generated from chronology, column A. Nothing needs to be adjusted.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.