SUMPRODUCT Help

[Brent H]

I need help with a text summing formula that incoperates two criteria over two worksheets. On worksheet One I have an account number in cells A1:A600, on worksheet Two I have corresponding account numbers in A1:A5000 (each account has multiple entries on this second worksheet). In addition on worksheet Two there is a question in cells B1:B5000 for each account with a Y or N answer in cells C1:C5000. I need a formula to enter on sheet one that will return the sum of Y answers for the account I specify in the formula.

 

[hiker95]

Brent H,

Welcome to the MrExcel forum.

Excel Workbook
	A	B	C
1	1	?	Y
2	2	?
3	3	?	Y
4	1	?	Y
5	2	?	Y
6	3	?
7	1	?	Y
8	2	?	Y
9	3	?	Y
10	7	?	Y
11	7	?
12	7	?	Y
13	7	?
14	7	?	Y
15	7	?	Y
16	7	?	Y
17
Sheet2

Excel Workbook
	A	B	C	D	E
1	1		Account Nbr	Y or N	Sum
2	2		7	Y	5
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11
Sheet1

 

[jim may]

Excel Workbook
	A	B	C
1	100		3
2	200		1
3	300		0
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(Sheet2!$A$1:$A$10=Sheet1!A1),--(Sheet2!$C$1:$C$10="Y"))
C2	=SUMPRODUCT(--(Sheet2!$A$1:$A$10=Sheet1!A2),--(Sheet2!$C$1:$C$10="Y"))
C3	=SUMPRODUCT(--(Sheet2!$A$1:$A$10=Sheet1!A3),--(Sheet2!$C$1:$C$10="Y"))

Excel Workbook
	A	B	C
1	100	Q1	Y
2	200	Q2	N
3	200	Q3	N
4	200	Q4	Y
5	100	Q1	N
6	300	Q2	N
7	100	Q3	Y
8	300	Q4	N
9	200	Q1	N
10	100	Q2	Y
Sheet2

Excel 2007

 

[Brent H]

Thank you. It worked perfectly