Total time spent with client

[brianfosterblack]

so I want to know if I can do this as a formula or do I need to run a macro
I want to add up the time spent on each client in each month
On my "Clients" Sheet I have all my client listed in Column C from row 4 downwards
In row 3 from columns "I" to "T" I have the 12 months starting from Mar and ending with Feb (This abbreviated version of the months corresponds to the sheet name for each month)

Lets just take the "Mar" sheet
The client name which corresponds to the client name in my Client Sheet is in Column A from A6 Downwards
The days of the month are in column "E" to "AI" in row 5
Each row in the month only has 1 client in that row and 1 time under 1 of the days in the month
I need on my Client Sheet to add up all the time spent on that client in Mar month and then for each other month in the column for that month.

Can this be done as a formula or will I need to run a macro
I

 

[etaf]

Quite a complicated formula using indirect
BUT assuming the sheet is named - MAR , APR , FEB etc
Also that the dates in the client Sheet are entered as TEXT and NOT dates
then this should work
=SUM(VLOOKUP($C4, INDIRECT("'"&D$3&"'!$A$6:$AI$30"),{5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35}, FALSE))

I have uploaded the Sheet onto dropbox , as well as using XL2BB here

Dropbox - File Deleted - Simplify your life

www.dropbox.com

NOT I have only added the MAR & APR Sheets - hence REF errors on other months

if the headers are real dates then it can still be done but a complicated formula
=SUMIFS(INDIRECT("'"&CHOOSE(MONTH(D$3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!$E$6:$AI$30"),INDIRECT("'"&CHOOSE(MONTH(D$3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!$A$6:$A$30"),$C4) INDIRECT("'"&CHOOSE(MONTH(D$3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!$E$6:$AI$30"

SUM Clients (ETAF).xlsx
	C	D	E	F	G	H
3		MAR	APR	MAY	JUN	JUL
4	client1	4	800	#REF!	#REF!	#REF!
5	client2	400	8000	#REF!	#REF!	#REF!
6	client3	0	0	#REF!	#REF!	#REF!
7	client4	0	0	#REF!	#REF!	#REF!
8	client5	0	0	#REF!	#REF!	#REF!
9	client6	0	0	#REF!	#REF!	#REF!
10	client7	0	0	#REF!	#REF!	#REF!
11	client8	0	0	#REF!	#REF!	#REF!
12	client9	0	0	#REF!	#REF!	#REF!
client

Cell Formulas
Range	Formula
D4:H12	D4	=SUM(VLOOKUP($C4, INDIRECT("'"&D$3&"'!$A$6:$AI$30"),{5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35}, FALSE))

SUM Clients (ETAF).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4
5					3/1/20	3/2/20	3/3/20	3/4/20	3/5/20	3/6/20	3/7/20	3/8/20	3/9/20	3/10/20	3/11/20	3/12/20	3/13/20	3/14/20	3/15/20	3/16/20
6	client1				1				1						1					1
7	client2					100					100							100
8	client3
9	client4
10	client5
11	client6
12	client7
13	client8
14	client9
15
16
mar

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[brianfosterblack]

I got the formula to work on my client sheet by changing the letter "D" to and I as my months start in column "I" on this sheet. But it is only looking up and finding the fist instance of the client. On the monthly sheet a clients name could be repeated multiple times but the times could be in any of the columns as you have identified.

 

[etaf]

then need to change to sumproduct if multiple rows
=SUMPRODUCT((INDIRECT("'"&D$3&"'!$E$6:$AI$1000"))*(INDIRECT("'"&D$3&"'!$A$6:$A$1000")=client!$C4))

need to relook at D to I , but a bit later after my dinner

update the share

Dropbox - File Deleted - Simplify your life

www.dropbox.com

SUM arange based on column of criteria.xlsx
	C	D	E	F	G	H
3		MAR	APR	MAY	JUN	JUL
4	client1	404	8800	#REF!	#REF!	#REF!
5	client2	0	0	#REF!	#REF!	#REF!
6	client3	0	0	#REF!	#REF!	#REF!
7	client4	0	0	#REF!	#REF!	#REF!
8	client5	0	0	#REF!	#REF!	#REF!
9	client6	0	0	#REF!	#REF!	#REF!
10	client7	0	0	#REF!	#REF!	#REF!
11	client8	0	0	#REF!	#REF!	#REF!
12	client1	404	8800	#REF!	#REF!	#REF!
client

Cell Formulas
Range	Formula
D4:E12	D4	=SUMPRODUCT((INDIRECT("'"&D$3&"'!$E$6:$AI$1000"))*(INDIRECT("'"&D$3&"'!$A$6:$A$1000")=client!$C4))
F4:H12	F4	=SUM(VLOOKUP($C4, INDIRECT("'"&F$3&"'!$A$6:$AI$30"),{5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35}, FALSE))

 

[etaf]

Ok, I see missed that in your first post - sorry
I have updated the share version
and heres the XL2BB

SUM arange based on column of criteria.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
3							MAR	APR	MAY	JUN	JUL	AUG
4	client1						404	8800	#REF!	#REF!	#REF!
5	client2						0	0	#REF!	#REF!	#REF!
6	client3						0	0	#REF!	#REF!	#REF!
7	client4						0	0	#REF!	#REF!	#REF!
8	client5						0	0	#REF!	#REF!	#REF!
9	client6						0	0	#REF!	#REF!	#REF!
10	client7						0	0	#REF!	#REF!	#REF!
11	client8						0	0	#REF!	#REF!	#REF!
12	client1						404	8800	#REF!	#REF!	#REF!
client

Cell Formulas
Range	Formula
I4:J12	I4	=SUMPRODUCT((INDIRECT("'"&I$3&"'!$E$6:$AI$1000"))*(INDIRECT("'"&I$3&"'!$A$6:$A$1000")=client!$C4))
K4:M12	K4	=SUM(VLOOKUP($C4, INDIRECT("'"&K$3&"'!$A$6:$AI$30"),{5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35}, FALSE))

 

[etaf]

i had a message from drop box, which i will keep the messages on the forum, rather than reply
I have updated the spreadsheet now, as mentioned on earlier reply - with sumproduct and a different name - see the link
I did mention if the Header is an actual date , then we can use a choose() to use that info
CHOOSE(MONTH(I$3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
So easy to change and use the date from the header to lookup the sheet name
BUT I needed to see you data layout , and di mention if you could put on a share like dropbox OR use XL2BB add-in

modified to use MONTH from a real date on client sheet

SUMPRODUCT based on column of criteria.xlsx
	C	D	E	F	G	H	I	J	K
3							3/1/20	4/1/20	MAY
4	client1						404	8800	#VALUE!
5	client2						0	0	#VALUE!
6	client3						0	0	#VALUE!
7	client4						0	0	#VALUE!
8	client5						0	0	#VALUE!
9	client6						0	0	#VALUE!
10	client7						0	0	#VALUE!
11	client8						0	0	#VALUE!
12	client1						404	8800	#VALUE!
client

Cell Formulas
Range	Formula
I4:K12	I4	=SUMPRODUCT((INDIRECT("'"&CHOOSE(MONTH(I$3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!$E$6:$AI$1000"))*(INDIRECT("'"&CHOOSE(MONTH(I$3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!$A$6:$A$1000")=client!$C4))

 

[brianfosterblack]

=SUMPRODUCT((INDIRECT("'"&I$3&"'!$E$6:$AI$1000"))*(INDIRECT("'"&I$3&"'!$A$6:$A$1000")=client!$C4))

I used this formula as I kept my headings on the Client sheet as Text. I was getting a &REF! error until I realised my sheet name is Clients not Client. Fixed this and it works like a charm. Thank you so much for your trouble and your patience with me as I still have to learn how to use many of these resources correctly

 

[etaf]

you are very welcome