Summarizing a teams capacity using percentages

[ExcelNovice2017]

Hi all

I'm doing a basic table to captured peoples capacity by percentage, per Week, it s very high-level where they will manually input there own percentages, example below and its all estimated it not base on day or hours

I would then like to summarize that by using a formula in a separate tab and highlight if the total percentage goes over 100 use Red, 80-100 = Green, and 50 - 70 Amber, , I was just going to use conditional formatting for the colours, but was getting stuck on the formula I will have more than 2 superhero's in the team. this is just to support a high-level view to see where the team would be stretch in the first have of the year

I was hoping to have the end result looking like the 2nd table

Excel Version 2018 if that s helps


Thank you in advance, I really do appreciate the help!

 

[etaf]

you dont say what version of excel you have - As a FILTER may work if on 2021/365 versions

=SUMPRODUCT(($B$3:$B$10=$B14)*($D$1:$J$1=D$13)*(Sheet1!$D$3:$J$10))

Book6
	A	B	C	D	E	F	G	H
1				1/2/23	1/9/23	1/16/23	1/23/23
2		batman		0	10	0	0
3		superman		20	0	20	20
4		batman		30	30	30	30
5		superman		0	0	0	0
6		batman		40	40	40	40
7		superman		50	50	50	50
8		batman		0	0	0	0
9		superman		12
10
11
12
13				1/2/23	1/9/23	1/16/23	1/23/23
14		batman		70	70	70	70
15		superman		82	50	70	70
16
17
Sheet1

Cell Formulas
Range	Formula
E1:G1	F1	=E1+7
E2	E2	=D2+10
D13:G13	D13	=D1
D14:G15	D14	=SUMPRODUCT(($B$3:$B$10=$B14)*($D$1:$J$1=D$13)*(Sheet1!$D$3:$J$10))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -


A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[ExcelNovice2017]

Hi etaf

Thank you for replying and offering a solution, unfortunately I'm getting a spill error but I image that's because you need a few more details from me as mentioned above and to make it easier

I have uploaded 2 mini file: 1. Data Input tab 2.Summary tab

also just to confirm I'm on Subscription Product (Mircosoft 365 Apps for enterprise) - version 2108

Data Input Tab

Test Resource Planner.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
4					02/01/2023	09/01/2023	16/01/2023	23/01/2023	30/01/2023	06/02/2023	13/02/2023	20/02/2023	27/02/2023	06/03/2023	13/03/2023	20/03/2023	27/03/2023	03/04/2023	10/04/2023	17/04/2023	24/04/2023	01/05/2023	08/05/2023	15/05/2023	22/05/2023	29/05/2023	05/06/2023
5	No	TASK	NAME	%	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
6	1	Admin and Meetings	Bob		50	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
7	2	Process Development	Bob		0	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50
8	3	Workshops	Bob		0	20	20	20	20	20	20	20	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	4	Appointments Module	Bob		50	5	5	5	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	5	Task Management - SOP	Bob		0	15	15	15	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	6	Van Stock Module	Bob		0	10	10	10	10	20	20	20	20	20	20	20	10	10	10	10	10	10	10	10	10	10	10
12	7	Schedule Of Rates Module	Bob		0	0	0	0	0	10	10	10	10	10	10	10	20	20	20	20	20	20	30	30	30	30	30
13	8	Billing Module	Dave		0	0	0	0	0	0	0	0	0	0	0	0	0	15	15	15	15	15	15	15	15	15	15
14	9	At your Service Module	Dave		0	0	0	0	0	0	0	0	0	0	0	0	0	15	15	15	15	15	15	15	15	15	15
15	10	Appointment - SOP	Dave		0	0	0	0	0	0	0	0	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
16	11	Billing - SOP	Dave		10	10	10	10	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17	12	SOR - SOP	Tom		20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	5
18	13	Van Stock - SOP	Tom		25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	25	5
19	14	GO - SOP	Tom		10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	5
20	15	PPM - SOP	Tom		0	0	0	0	0	0	0	0	0	0	0	0	0	5	5	5	5	5	5	5	5	5	5
21	16	DevOps - SOP	Tom		0	0	0	0	0	0	0	0	0	0	0	0	0	5	5	5	5	5	5	5	5	5	5
22	17	Test Strategy- SOP	Alan		0	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50
23	18	Sharepoint- SOP	Alan		0	20	20	20	20	20	20	20	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24	19	Process Change - SOP	Alan		0	0	0	0	0	0	0	0	0	0	0	0	0	5	5	5	5	5	5	5	5	5	5
25	20	Training - SOP	Alan		0	0	0	0	0	0	0	0	0	0	0	0	0	5	5	5	5	5	5	5	5	5	5
26	21	Mobilisation - SOP	Alan		0	0	0	0	0	0	0	0	0	0	0	0	0	5	5	5	5	5	5	5	5	5	5
27	22	Change Requests - SOP	Ringo		0	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50
28	23	Commercial - SOP	Ringo		0	20	20	20	20	20	20	20	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
29	24	Projects - SOP	Ringo		0	0	0	0	0	0	0	0	0	25	25	25	25	25	25	25	25	25	25	5	5	5	5
30	25	Folder Re-structure - Share Point	Ringo		0	0	0	0	0	0	0	0	0	10	10	10	10	10	10	10	10	10	10	5	5	5	5
31	26	Sharepoint Homepage	Ringo		0	0	0	0	10	0	0	0	0	0	0	0	0	5	5	5	5	5	5	5	5	5	5
32	27	Training Guides	Ringo		0	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
Data Input

Cell Formulas
Range	Formula
G4:AB4	G4	=F4+7

Summary Tab

Test Resource Planner.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
3				02/01/2023	09/01/2023	16/01/2023	23/01/2023	30/01/2023	06/02/2023	13/02/2023	20/02/2023	27/02/2023	06/03/2023	13/03/2023	20/03/2023	27/03/2023	03/04/2023	10/04/2023	17/04/2023	24/04/2023	01/05/2023	08/05/2023	15/05/2023	22/05/2023	29/05/2023	05/06/2023
4	No	NAME	%	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
5	1	Bob		#SPILL!
6	2	Dave		#SPILL!
7	3	Tom		#SPILL!
8	4	Alan		#SPILL!
9	5	Ringo		#SPILL!
10	6	Spare		#SPILL!
11	7	Spare		#SPILL!
12	8	Spare		#SPILL!
13	9	Spare		#SPILL!
14	10	Spare
15	11	Spare
Summary

Cell Formulas
Range	Formula
G3:AB3	G3	=F3+7
F5:F13	F5	=SUMPRODUCT('Data Input'!$D$6:$D$105=Summary!$D5)*('Data Input'!$F$4:$BE$4=Summary!$F3)*('Data Input'!$F$6:$BE$105)

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "No"}, {"Column2", "Task"}, {"Column3", "Name"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column4", "No", "Task"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

Name	1/2/2023	1/9/2023	1/16/2023	1/23/2023	1/30/2023	2/6/2023	2/13/2023	2/20/2023	2/27/2023	3/6/2023	3/13/2023	3/20/2023	3/27/2023	4/3/2023	4/10/2023	4/17/2023	4/24/2023	5/1/2023	5/8/2023	5/15/2023	5/22/2023	5/29/2023	6/5/2023
Alan	0	70	70	70	70	70	70	70	50	50	50	50	50	65	65	65	65	65	65	65	65	65	65
Bob	100	105	105	105	90	105	105	105	85	85	85	85	85	85	85	85	85	85	95	95	95	95	95
Dave	10	10	10	10	10	0	0	0	5	5	5	5	5	35	35	35	35	35	35	35	35	35	35
Ringo	0	75	75	75	85	75	75	75	55	90	90	90	90	95	95	95	95	95	95	70	70	70	70
Tom	55	55	55	55	55	55	55	55	55	55	55	55	55	65	65	65	65	65	65	65	65	65	25

 

[Fluff]

I'm on Subscription Product (Mircosoft 365 Apps for enterprise) - version 2108

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You are missing some brackets from the formula, it should be

=SUMPRODUCT(('Data Input'!$D$6:$D$105=$D5)*('Data Input'!$F$4:$BE$4=F$3)*('Data Input'!$F$6:$BE$105))

 

[ExcelNovice2017]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You are missing some brackets from the formula, it should be

=SUMPRODUCT(('Data Input'!$D$6:$D$105=$D5)*('Data Input'!$F$4:$BE$4=F$3)*('Data Input'!$F$6:$BE$105))

Thank you for the tips Fluff - I have updated my preferences

and thank you for highlighting that I have forgotten to add some brackets, works as intended now

 

[ExcelNovice2017]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "No"}, {"Column2", "Task"}, {"Column3", "Name"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column4", "No", "Task"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

Name	1/2/2023	1/9/2023	1/16/2023	1/23/2023	1/30/2023	2/6/2023	2/13/2023	2/20/2023	2/27/2023	3/6/2023	3/13/2023	3/20/2023	3/27/2023	4/3/2023	4/10/2023	4/17/2023	4/24/2023	5/1/2023	5/8/2023	5/15/2023	5/22/2023	5/29/2023	6/5/2023
Alan	0	70	70	70	70	70	70	70	50	50	50	50	50	65	65	65	65	65	65	65	65	65	65
Bob	100	105	105	105	90	105	105	105	85	85	85	85	85	85	85	85	85	85	95	95	95	95	95
Dave	10	10	10	10	10	0	0	0	5	5	5	5	5	35	35	35	35	35	35	35	35	35	35
Ringo	0	75	75	75	85	75	75	75	55	90	90	90	90	95	95	95	95	95	95	70	70	70	70
Tom	55	55	55	55	55	55	55	55	55	55	55	55	55	65	65	65	65	65	65	65	65	65	25

Cheers Alan - For the different approach, I shall definitely keep this in mind and give it a go, It's been a while since I have done anything in excel let alone power query so It will be good practice if nothing else

 

[Fluff]

Glad we could help & thanks for the feedback