sumif & sumproduct

[MichelleT]

Hi, can someone help me with the formula to get the total of column C in
sheet 2 if column B in sheet 2 is Y (reference to sheet 1)

sheet 1
A B
1 a Y
2 b Y
3 c N

sheet 2
A B C
1 x a 10
2 x b 15
3 y c 18

thanks
Michelle

 

[Sam Wilson]

Put this in sheet 2:

=SUMIF(B:B,"Y","C:C")

 

[Sam Wilson]

=SUMIF(B:B,"Y",C:C)

Ignore the last one with the typo

 

[Ashish Mathur]

Hi,

Your question is not clear. Anyway, try this

=sumproduct((sheet2!A1:A3=B1)*(sheet2!B1:B3=A1)*(sheet2!C1:C3))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[MichelleT]

How does the formula identify whether text in column B is Y/N when the
formula does not make any reference to sheet 1

 

[Bob Phillips]

If you are saying there is a cell in Sheet1 with the Y in it, use

=SUMIF(B:B,Sheet1!H1,C:C)

 

[MichelleT]

e.g total for x is only 25 and not 45 because a & b = Y and C = N in sheet 1
Hope eg explains better

sheet 24 x c 20

 

[Bernd P]

Hello Michelle,

Enter into C1 in Sheet2:
=SUMPRODUCT(--("Y"=LOOKUP(A1:A4,Sheet1!A1:A3,Sheet1!B1:B3)),B1:B4)

Please note that values in Sheet1!A1:A3 have to be sorted in ascending
order.

Regards,
Bernd