to find the sum of rows on multiple condition

[klnp_mysore]

Hi,
I have two work sheets. Sheet1 and Sheet2.

Sheet1
Col A Col B Col C Col D
aaa-1 abc 2000
aaa-2 abc 500
aaa-3 abc
bbb-1 xyz 1000
bbb-2 xyz
bbb-3 xyz

Sheet 2
Col A Col B Col C Col D
aaa-1 abc Main 1000
aaa-1 abc Main 1000
aaa-2 abc Neu 500
bbb-1 xyz Main 1000

I want to find the sum in sheet 1 if row aaa-1 in sheet1 and aaa-1 in sheet 2 are equal and col C is Main
Please help me

Regards

 

[Akashwani]

Hi,

Excel Workbook
	A	B	C	D
1	Data1	Data2	Data3	Data4
2	aaa-1	abc	main	1000
3	aaa-1	abc	main	1000
4	aaa-1	abc	neu	500
5	bbb-1	xyz	main	1000
Sheet2

Excel Workbook
	A	B	C	D
1	Data1	Data2	Data3	MAIN
2	aaa-1	abc	2000	*
3	aaa-2	abc	0	*
4	aaa-3	abc	0	*
5	bbb-1	xyz	1000	*
6	bbb-2	xyz	0	*
7	bbb-3	xyz	0	*
Sheet1

Or Excel 2007....

Excel Workbook
	A	B	C	D
1	Data1	Data2	Data3	MAIN
2	aaa-1	abc	2000	*
3	aaa-2	abc	0	*
4	aaa-3	abc	0	*
5	bbb-1	xyz	1000	*
6	bbb-2	xyz	0	*
7	bbb-3	xyz	0	*
Sheet1

You could use...

=SUMPRODUCT((Sheet2!$A$2:$A$5=A2)*(Sheet2!$C$2:$C$5="main")*(Sheet2!$D$2:$D$5))

=SUMIFS(Sheet2!$D$2:$D$5,Sheet2!$A$2:$A$5,A2,Sheet2!$C$2:$C$5,"main")

Does that work for you?

Ak