# Summarizing a teams capacity using percentages



## ExcelNovice2017 (Jan 6, 2023)

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 (Jan 7, 2023)

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))

Book6ABCDEFGH11/2/231/9/231/16/231/23/232batman010003superman20020204batman303030305superman00006batman404040407superman505050508batman00009superman12101112131/2/231/9/231/16/231/23/2314batman7070707015superman825070701617Sheet1Cell FormulasRangeFormulaE1:G1F1=E1+7E2E2=D2+10D13:G13D13=D1D14:G15D14=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 (Monday at 5:37 AM)

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.xlsxBCDEFGHIJKLMNOPQRSTUVWXYZAAAB402/01/202309/01/202316/01/202323/01/202330/01/202306/02/202313/02/202320/02/202327/02/202306/03/202313/03/202320/03/202327/03/202303/04/202310/04/202317/04/202324/04/202301/05/202308/05/202315/05/202322/05/202329/05/202305/06/20235NoTASKNAME%123456789101112131415161718192021222361Admin and MeetingsBob50555555555555555555555572Process DevelopmentBob05050505050505050505050505050505050505050505083WorkshopsBob02020202020202000000000000000094Appointments ModuleBob505555000000000000000000105Task Management - SOPBob01515150000000000000000000116Van Stock Module Bob010101010202020202020201010101010101010101010127Schedule Of Rates Module Bob00000101010101010102020202020203030303030138Billing ModuleDave000000000000015151515151515151515149At your Service ModuleDave0000000000000151515151515151515151510Appointment - SOPDave000000005555555555555551611Billing - SOPDave10101010100000000000000000001712SOR - SOPTom2020202020202020202020202020202020202020202051813Van Stock - SOPTom2525252525252525252525252525252525252525252551914GO - SOPTom1010101010101010101010101010101010101010101052015PPM - SOPTom000000000000055555555552116DevOps - SOPTom000000000000055555555552217Test Strategy- SOPAlan0505050505050505050505050505050505050505050502318Sharepoint- SOPAlan0202020202020200000000000000002419Process Change - SOPAlan000000000000055555555552520Training - SOPAlan000000000000055555555552621Mobilisation - SOPAlan000000000000055555555552722Change Requests - SOPRingo0505050505050505050505050505050505050505050502823Commercial - SOPRingo0202020202020200000000000000002924Projects - SOPRingo0000000002525252525252525252555553025Folder Re-structure - Share PointRingo0000000001010101010101010101055553126Sharepoint HomepageRingo0000100000000055555555553227Training GuidesRingo05555555555555555555555Data InputCell FormulasRangeFormulaG4:AB4G4=F4+7

Summary Tab
Test Resource Planner.xlsxCDEFGHIJKLMNOPQRSTUVWXYZAAAB302/01/202309/01/202316/01/202323/01/202330/01/202306/02/202313/02/202320/02/202327/02/202306/03/202313/03/202320/03/202327/03/202303/04/202310/04/202317/04/202324/04/202301/05/202308/05/202315/05/202322/05/202329/05/202305/06/20234NoNAME%123456789101112131415161718192021222351Bob#SPILL!62Dave#SPILL!73Tom#SPILL!84Alan#SPILL!95Ringo#SPILL!106Spare#SPILL!117Spare#SPILL!128Spare#SPILL!139Spare#SPILL!1410Spare1511SpareSummaryCell FormulasRangeFormulaG3:AB3G3=F3+7F5:F13F5=SUMPRODUCT('Data Input'!$D$6:$D$105=Summary!$D5)*('Data Input'!$F$4:$BE$4=Summary!$F3)*('Data Input'!$F$6:$BE$105)


----------



## alansidman (Monday at 5:52 AM)

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"
```

Name1/2/20231/9/20231/16/20231/23/20231/30/20232/6/20232/13/20232/20/20232/27/20233/6/20233/13/20233/20/20233/27/20234/3/20234/10/20234/17/20234/24/20235/1/20235/8/20235/15/20235/22/20235/29/20236/5/2023Alan070707070707070505050505065656565656565656565Bob10010510510590105105105858585858585858585859595959595Dave10101010100005555535353535353535353535Ringo075757585757575559090909095959595959570707070Tom5555555555555555555555555565656565656565656525


----------



## Fluff (Monday at 6:03 AM)

ExcelNovice2017 said:


> 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 (Monday at 7:38 AM)

Fluff said:


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


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 (Monday at 7:40 AM)

alansidman said:


> Power Query Solution
> 
> 
> ```
> ...


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 (Monday at 7:43 AM)

Glad we could help & thanks for the feedback


----------

