Formula to sum by month

[brianfosterblack]

I need a formula to total the hours (in another worksheet but in the same workbook) I have spent on a client
In my workbook I have a "Master" sheet and from cell Master!23 downwards I have a list of all my Client worksheets in the workbooks.
Names like BigBlue, MVP, MyGas, etc. The length of this list will vary but all sheet names are 1 word.
Next to these names I have 12 columns, 1 for each month of the year - Jan to Dec
On each Client worksheet from row 4 downwards I have in column Z the date (Date format dd/mm/yyyy) I did work for the client and in column AA the hours and minutes spent on the client (Format hh:mm)
I need on the Master Sheet in each cell in the calendar a formula for each client which will add up the hours spent on the client for each month of the year.

Can someone assist with this formula.

 

[Toadstool]

Hi BrianFosterBlack,

You should be able to achieve this with INDIRECT. I assume your regional date format is dd/mm/yy and though mine is mm/dd/yy (a US PC) it should work with your data. Please note that all time cells should be formatted as Custom [h]:mm so they can handle accumulated time over 24 hours.

Here are my three data sheets for BigBlue, MyGas and MVP:

BrianFosterBlack.xlsx
	Z	AA
3	Date	Hours
4	02-Feb-21	12:45
5	03-Mar-21	9:00
6	03-Mar-21	3:30
7	05-May-21	2:45
8	06-Jun-21	12:00
9	09-Sep-21	1:00
10	30-Sep-21	3:00
11
BigBlue

BrianFosterBlack.xlsx
	Z	AA
3	Date	Hours
4	11-Jan-21	1:11
5	10-Feb-21	2:10
6	12-Mar-21	3:12
7	11-Apr-21	4:11
8	11-May-21	5:11
9	10-Jun-21	6:10
10	10-Jul-21	7:10
11	30-Aug-21	8:30
12	08-Sep-21	9:08
13	19-Oct-21	10:19
14	07-Nov-21	11:07
15	07-Dec-21	12:07
16
MyGas

BrianFosterBlack.xlsx
	Z	AA
3	Date	Hours
4	02-Feb-21	12:45
5	03-Mar-21	9:00
6	03-Mar-21	3:30
7	15-Mar-21	2:45
8	04-Apr-21	12:00
9	04-Apr-21	10:00
10	22-Apr-21	15:30
11
MVP

Here is my Master sheet with the Client names and calendar hours accumulated:

BrianFosterBlack.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
22	Clients	01-Jan-21	01-Feb-21	01-Mar-21	01-Apr-21	01-May-21	01-Jun-21	01-Jul-21	01-Aug-21	01-Sep-21	01-Oct-21	01-Nov-21	01-Dec-21
23	BigBlue	0:00	12:45	12:30	0:00	2:45	12:00	0:00	0:00	4:00	0:00	0:00	0:00
24	MVP	0:00	12:45	15:15	37:30	0:00	0:00	0:00	0:00	0:00	0:00	0:00	0:00
25	MyGas	1:11	2:10	3:12	4:11	5:11	6:10	7:10	8:30	9:08	10:19	11:07	12:07
26
Master

Cell Formulas
Range	Formula
C22:M22	C22	=EOMONTH(B22,0)+1
B23:M26	B23	=IF($A23="","",SUMIFS(INDIRECT($A23&"!$AA$4:$AA$999"),INDIRECT($A23&"!$Z$4:$Z$999"),">="&B$22,INDIRECT($A23&"!$Z$4:$Z$999"),"<="&EOMONTH(B$22,0)))

 

[brianfosterblack]

Hi BrianFosterBlack,

You should be able to achieve this with INDIRECT. I assume your regional date format is dd/mm/yy and though mine is mm/dd/yy (a US PC) it should work with your data. Please note that all time cells should be formatted as Custom [h]:mm so they can handle accumulated time over 24 hours.

Here are my three data sheets for BigBlue, MyGas and MVP:

BrianFosterBlack.xlsx
	Z	AA
3	Date	Hours
4	02-Feb-21	12:45
5	03-Mar-21	9:00
6	03-Mar-21	3:30
7	05-May-21	2:45
8	06-Jun-21	12:00
9	09-Sep-21	1:00
10	30-Sep-21	3:00
11
BigBlue

BrianFosterBlack.xlsx
	Z	AA
3	Date	Hours
4	11-Jan-21	1:11
5	10-Feb-21	2:10
6	12-Mar-21	3:12
7	11-Apr-21	4:11
8	11-May-21	5:11
9	10-Jun-21	6:10
10	10-Jul-21	7:10
11	30-Aug-21	8:30
12	08-Sep-21	9:08
13	19-Oct-21	10:19
14	07-Nov-21	11:07
15	07-Dec-21	12:07
16
MyGas

BrianFosterBlack.xlsx
	Z	AA
3	Date	Hours
4	02-Feb-21	12:45
5	03-Mar-21	9:00
6	03-Mar-21	3:30
7	15-Mar-21	2:45
8	04-Apr-21	12:00
9	04-Apr-21	10:00
10	22-Apr-21	15:30
11
MVP

Here is my Master sheet with the Client names and calendar hours accumulated:

BrianFosterBlack.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
22	Clients	01-Jan-21	01-Feb-21	01-Mar-21	01-Apr-21	01-May-21	01-Jun-21	01-Jul-21	01-Aug-21	01-Sep-21	01-Oct-21	01-Nov-21	01-Dec-21
23	BigBlue	0:00	12:45	12:30	0:00	2:45	12:00	0:00	0:00	4:00	0:00	0:00	0:00
24	MVP	0:00	12:45	15:15	37:30	0:00	0:00	0:00	0:00	0:00	0:00	0:00	0:00
25	MyGas	1:11	2:10	3:12	4:11	5:11	6:10	7:10	8:30	9:08	10:19	11:07	12:07
26
Master

Cell Formulas
Range	Formula
C22:M22	C22	=EOMONTH(B22,0)+1
B23:M26	B23	=IF($A23="","",SUMIFS(INDIRECT($A23&"!$AA$4:$AA$999"),INDIRECT($A23&"!$Z$4:$Z$999"),">="&B$22,INDIRECT($A23&"!$Z$4:$Z$999"),"<="&EOMONTH(B$22,0)))

Wow! This works perfectly - Thank you so much

 

[Toadstool]

Wow!

Wow! This works perfectly - Thank you so much

You're welcome.
Thanks for the feedback.