Project Hours - Weekly Reporting

[mystique07]

We have multiple projects and I need to report the weekly consumed hours each week.

i have used SUMIFS to get the monthly totals. but don't know how to get the consumed hours as of each week which should be total hours - consumed till date.

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Project	Jun	Jul	Aug	4-Sep	11-Sep	18-Sep	25-Sep
2	Name 1	ABC	100	50	100	40	40	40	40
3	Name 2	ABC	100	50	100	20	20	20	20
4	Name 2	MNC	100	50	100	20	20	20	20
Sheet1

 

[alansidman]

What do you want your end result to look like?

 

[mystique07]

Hi,

I need something like the below so that I can report it at the end of the week.

Help me only with calculating "Consumed - As of 9/4"

	Total Hours	Consumed - As of 9/4	Available
Project Name - ABC	500	300	200

 

[alansidman]

Ok. Where are the Total Hours coming from? They do not appear in your first table.

 

[mystique07]

Look at Column F - Sep. That's how I calculate the monthly totals.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Project	Jun	Jul	Aug	Sep	4-Sep	11-Sep	18-Sep	25-Sep
2	Name 1	ABC	100	50	100	160	40	40	40	40
3	Name 2	ABC	100	50	100	80	20	20	20	20
4	Name 2	MNC	100	50	100	80	20	20	20	20
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=SUMIFS(Table1[@[4-Sep]:[25-Sep]],Table1[[#Headers],[4-Sep]:[25-Sep]],"*Sep*")

 

[alansidman]

I think you missed understanding my question. In post #3, you have a column indicating Total Hours. This is not to be found in your original post. I assume this is your budget for the project. Is that correct. If so, where does it come from so that it can be incorporated into determining the Available Hours remaining?

 

[mystique07]

That total hour is determined before a project start using various factors. I enter that manually to calculate the remaining hours

 

[alansidman]

If you wish to have it as part of the solution you are looking for, you need to provide it to us. How else can we give you the expected solution you have shown

 

[mystique07]

Can you help me with the below. the totals are calculated from the monthly hours

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Project	Total	Jun	Jul	Aug	Sep	4-Sep	11-Sep	18-Sep	25-Sep
2	Name 1	ABC	410	100	50	100	160	40	40	40	40
3	Name 2	ABC	330	100	50	100	80	20	20	20	20
4	Name 2	MNC	330	100	50	100	80	20	20	20	20
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=SUM(Table1[@[Jun]:[Sep]])
G2:G4	G2	=SUMIFS(Table1[@[4-Sep]:[25-Sep]],Table1[[#Headers],[4-Sep]:[25-Sep]],"*Sep*")

 

[mystique07]

@alansidman I have updated the table with the totals. can you please help with my request