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

Put this in sheet 2:

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

"MichelleT" wrote:

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

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

Ignore the last one with the typo

"MichelleT" wrote:

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

Hi,

Your question is not clear. Anyway, try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MichelleT" wrote in message
...
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

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

"Sam Wilson" wrote:

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

Ignore the last one with the typo

"MichelleT" wrote:

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

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

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


--
__________________________________
HTH

Bob

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

"Sam Wilson" wrote:

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

Ignore the last one with the typo

"MichelleT" wrote:

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

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 2
A B C
1 x a 10
2 x b 15
3 y c 18
4 x c 20

"Ashish Mathur" wrote:

Hi,

Your question is not clear. Anyway, try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MichelleT" wrote in message
...
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

Hello Michelle,

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

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

Regards,
Bernd