Summarise result of Sumifs using different criteria

PaulyK

Board Regular
Joined
Aug 27, 2015
Messages
50
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi,
I have created a Project Summary Resourcing Report (with thanks to @Fluff ) which uses SUMIFS to get a total of the days worked by a person, per month, per project. The month is selected via a dropdown list.

The problem is have is that in the main report there are upwards of 100 Projects and the same for People, which is then hard to summarise easily.
I would like to be able to select a specific person and show the projects they worked on and the days worked for that month (or overall) I thought I could achieve this via a dropdown list of People and then the data would show and would change based on month / person. Additionally if the same could be achieved Selecting the Project to get a list of people...but that isn't a priority)

I understand using an XLOOKUP I can display a whole column as a result, so wondered if this was an option to display the results, but not sure where to start; or if an easier way!

Summary would look something like this (or similar - but don't mind how it looks, just need it to do what i want)
Resource Allocation Tool - RAT.xlsx
ABCD
2NamePerson 1
3DateMay
4ProjectsProject 13
5Project 23
6Project 31.5
7Project 65
8Project 102
9
10Total14.5
Summary
Cell Formulas
RangeFormula
C10C10=SUBTOTAL(9,C4:C8)
Cells with Data Validation
CellAllowCriteria
B2List=IT_Team
B3List=rngMonths


Summary By Resource
Resource Allocation Tool - RAT.xlsx
ABCDEFGHIJKLMNOPQRS
1Resource Summary (Demand / Pipeline)
2Select PeriodMay-22
3
4TeamResourceProject 1Project 10Project 11Project 12Project 13Project 14Project 15Project 16Project 2Project 3Project 4Project 5Project 6Project 7Project 8Project 90
5Team 1Person 1000000020000000000
6Team 1Person 200000000000007.5000
7Team 2Person 3000000000081400000
8Team 1Person 4100001100200000000
9Team 2Person 5000000000018400000
10Team 3Person 60000110000010000000
11Team 1Person 7000000000012000000
12Team 2Person 800400000000000000
13Team 2Person 90012.500000000000050
14Team 2Person 100.50000000000000000
15Team 4Person 1150000000000000000
16Team 5Person 1200000000010000000
17Team 4Person 1300000005008700000
18Team 1Person 1400000000040000000
19Team 1Person 1500000000001.52000000
20Team 1Person 1600000000000000000
21Team 5Person 17000000000002001000
22Team 1Person 18000000100000000000
23Team 2Person 19000160000030000000
24Team 2Person 20000000015000000000
Summary (Pipeline) by Resource
Cell Formulas
RangeFormula
C4:S4C4=TRANSPOSE(Lists!H2#)
B5:B24B5=UNIQUE(Lists!G2:G21)
C5:S24C5=SUMIFS(INDEX(TblNewProjAlloc[[Jan-22]:[Sep-22]],,MATCH(TEXT($B$2,"mmm-yy"),TblNewProjAlloc[[#Headers],[Jan-22]:[Sep-22]],0)),TblNewProjAlloc[[WHO]:[WHO]],$B5#,TblNewProjAlloc[[PROJECT NAME]:[PROJECT NAME]],C4)
A5:A20,A22:A24A5=IFERROR(INDEX(NameTbl[Team],MATCH('Summary (Pipeline) by Resource'!B5,NameTbl[Name],0))," ")&""
A21A21=IFERROR(INDEX(NameTbl[Team],MATCH('Summary (Pipeline) by Resource'!B21,NameTbl[Name],0))," ")
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Project allocation (Pipeline)'!IT_Team=NameTbl[Name]A5:A24
'Summary - Pipeline (Days)'!IT_Team=NameTbl[Name]A5:A24
'Summary (Pipeline) by Resource'!IT_Team=NameTbl[Name]A5:A24
IT_Team=NameTbl[Name]A5:A24
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C6:S24Cell Value>0textNO
C5:S5Cell Value>0textNO
Cells with Data Validation
CellAllowCriteria
B2List=rngMonths


Source Data
Resource Allocation Tool - RAT.xlsx
ABCDEGHIJKL
2STAGEPROJECT NAMEWHOJan-22Feb-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22
3DemandProject 1Person 84442
4DemandProject 1Person 1832221
5DemandProject 1Person 1050.51212122
6DemandProject 1Person 154442
7DemandProject 1Person 1155555
8DemandProject 1Person 1655500
9DemandProject 1Person 4111
10DemandProject 1Person 250100
11DemandProject 2Person 41102222
12DemandProject 3Person 142342
13DemandProject 3Person 1933
14DemandProject 3Person 12111.51.51.53
15PipelineProject 4Person 1388888
16PipelineProject 4Person 388888
17PipelineProject 4Person 151.512121210
18PipelineProject 4Person 57.51820202020
19PipelineProject 4Person 67.510
20PipelineProject 4Person 77.512
21PipelineProject 5Person 137.570.5
22PipelineProject 5Person 37.51.5140.5
23PipelineProject 5Person 157.5200.5
24PipelineProject 5Person 51424
25PipelineProject 5Person 172
26PipelineProject 6Person 1315222
27PipelineProject 7Person 257.511111111
28PipelineProject 8Person 170.510101010
29PipelineProject 9Person 99557.5
30PipelineProject 10Person 20
31PipelineProject 11Person 844444
32PipelineProject 11Person 9912.55.555
33PipelineProject 12Person 190.51620201010
34PipelineProject 13Person 60.51111117.57.5
35PipelineProject 14Person 40.51111117.57.5
36PipelineProject 15Person 1891010101010
37PipelineProject 16Person 1112016161616
38PipelineProject 16Person 131155555
39PipelineProject 16Person 2011159811
40
Project allocation (Pipeline)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:L40Expression=$A3="Pipeline"textNO
A3:L40Expression=$A3="Demand"textNO
Cells with Data Validation
CellAllowCriteria
C3:C39List=IT_Team
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Not sure ifthe Title of this matches the request....
 
Upvote 0
I just need an easy way of being able to Summarise a large set of data.... I am sure the solution is simple but I can't work it out
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top