Vlookup & If & SUM Formula

[Cantrecallmyusername]

Hi there,

I have a sheet where I want to sum up hours for a person per month.
Tasks are entered on a row basis where the user enters the name in column C, hours per task for each month column H contains Jul, column H contains Aug, etc

On a separate sheet I want to sum all the hours for person A for each month.
I am not sure if this can be done in a vlookup, IF and SUM function as Vlookup will only return the 1st value.

I cant download the plugin to share excel file but here is an example of my dataset.
This is my raw data sheet

Name				Target Date	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23​	Dec-23​
Person A					12​	12​	12​	12​	12​	12​
Person A					4​	0​	1​	1​	1​	1​
Person A					14​	14​	14​	14​	14​	14​
Person A					26​	31​	0​	0​	0​	0​
Person B					7​	7​	0​	0​	0​	0​
Person B					40​	40​	0​	0​	0​	0​

My desired outcome is to have on a separate sheet the person name listed in column A and the dates across row 1 and a formula to pull back the sum values from the dataset above

Name Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23
Person A 56 57 27 27 27 27
Person B 47 47 0 0 0 0

I hope this makes sense and thank you in advance for your help.

 

[Cantrecallmyusername]

I have attempted to use the below formula and I believe I am going in the right direction but not quiet there yet.

=SUM(INDEX(WIP!$A$2:$K$6,1,MATCH(Sheet2!A2,WIP!A2:K6,1)))

 

[arthurbr]

Perhaps

=SUMPRODUCT(--(A2:A7="Person A")*FILTER(F2:K7;F1:K1=F1))

 

[Cantrecallmyusername]

Perhaps

=SUMPRODUCT(--(A2:A7="Person A")*FILTER(F2:K7;F1:K1=F1))

Hi thanks for the reply.
I am getting an error with that formula

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	Name	Target Date	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
2	Person A		12	12	12	12	12	12
3	Person A		4	0	1	1	1	1
4	Person A		14	14	14	14	14	14
5	Person A		26	31	0	0	0	0
6	Person B		7	7	0	0	0	0
7	Person B		40	40	0	0	0	0
8
9
10	Name		Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
11	Person A		56	57	27	27	27	27
12	Person B		47	47	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
C11:H12	C11	=SUMIF($A$2:$A$7,$A11,INDEX($C$2:$H$7,0,MATCH(C$10,$C$1:$H$1,0)))

 

[Cantrecallmyusername]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	Name	Target Date	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
2	Person A		12	12	12	12	12	12
3	Person A		4	0	1	1	1	1
4	Person A		14	14	14	14	14	14
5	Person A		26	31	0	0	0	0
6	Person B		7	7	0	0	0	0
7	Person B		40	40	0	0	0	0
8
9
10	Name		Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
11	Person A		56	57	27	27	27	27
12	Person B		47	47	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
C11:H12	C11	=SUMIF($A$2:$A$7,$A11,INDEX($C$2:$H$7,0,MATCH(C$10,$C$1:$H$1,0)))

Thats great - this worked for me as I required, thank you.

 

[arthurbr]

Hi thanks for the reply.
I am getting an error with that formula

View attachment 97297

That is because I omitted to replace the semi colon with a comma - Sorry
Try

=SUMPRODUCT(--(A2:A7="Person A")*FILTER(F2:K7,F1:K1=F1))

 

[Cantrecallmyusername]

That is because I omitted to replace the semi colon with a comma - Sorry
Try

=SUMPRODUCT(--(A2:A7="Person A")*FILTER(F2:K7,F1:K1=F1))

No problem at all
The initally issue is resolved however the formula is not returning the answer for me.