Sum if some condition match

[Vishaal]

Hi All,

We have the following sheets

Sheet1

Example (2).xlsx
	A	B	C	D	E
1	d & T	Name	OT	NEW	P/l
2	2/20/2018 0:38:52	Tom Van	5246	65214	258
3	2/26/2018 16:47:52		65230		2547
Sheet1

Sheet2

Example (2).xlsx
	A	B	C
1			02-20-2018
2	Tom Van	Test 1	pass
3		Test 2	o.k.
4		Test 3
5		Test 4
6		Test 5
Sheet2

we want the following result
if Sheet2!A2 will match Sheet1!B2:B3 and Sheet2!C1 date will match Sheet1!A2:A3 then we will get the sum of Sheet1!C2+D2
if Sheet2!A2 will match Sheet1!B2:B3 and Sheet2!C1 date will match Sheet1!A2:A3 then we will get the sum of Sheet1!E2

 

[Eric W]

Try:

Book2
	A	B	C	D	E
1			2/20/2018	Sum of C/D	Sum of E
2	Tom Van	Test 1	pass	70460	258
3		Test 2	o.k.
4		Test 3
5		Test 4
6		Test 5
Sheet2

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(Sheet1!$C$2:$D$5*(INT(Sheet1!$A$2:$A$5)=INT(C1))*(Sheet1!$B$2:$B$5=A2))
E2	E2	=SUMPRODUCT(Sheet1!$E$2:$E$5*(INT(Sheet1!$A$2:$A$5)=INT(C1))*(Sheet1!$B$2:$B$5=A2))

 

[Vishaal]

Thanks for your help Eric W

but its not working

pls check

Sheet1

Example.xlsx
	A	B	C	D	E
1	d & T	Name	OT	NEW	P/l
2	2/20/2018 0:38:52	Tom Van	5246	65214	258
3	2/26/2018 16:47:52		65230		2547
Sheet1

Result Sheet after using your formula in D2 & E2

Example.xlsx
	A	B	C	D	E
1			02-20-2018
2	Tom Van	Test 1	pass	#VALUE!	#VALUE!
3		Test 2	o.k.
4		Test 3
5		Test 4
6		Test 5
Sheet2

help pls

 

[Vishaal]

using ms excel 2010

 

[Vishaal]

Thanks for your help Eric W


its working

 

[Eric W]

Sounds like you got it to work. Let us know if you have more questions.

 

[Vishaal]

yes Wric W ji

its work for me thanks for your help