Hi,

I am working on a worksheet and i am not able to get the correct
values in the worksheet. The formula that i have give is as follows:

SUM(IF('FIRST'!$E$8:$E$4987="PRODUCT",
IF('FIRST'!$P$8:$P$4987="ANALYSIS",1,0)))

In this Formula, "First" is a work sheet and in the that sheet we
are filtering cells under PRODUCT , and from those PRODUCT cells which
has value ANALYSIS. But the final result that we are getting is not
correct "#Value#'. Can anyone help me in solving this problem.

Regard,
Rajeev Prabhu


--
rajeevprabhuat
------------------------------------------------------------------------
rajeevprabhuat's Profile: http://www.excelforum.com/member.php...o&userid=25380
View this thread: http://www.excelforum.com/showthread...hreadid=388579

Enter the formula as an arrayformula. i.e. after entering the formula, press
control shift enter

Mangesh



"rajeevprabhuat"
<rajeevprabhuat.1sgciq_1121836049.5538@excelforu m-nospam.com wrote in
message news:rajeevprabhuat.1sgciq_1121836049.5538@excelfo rum-nospam.com...

Hi,

I am working on a worksheet and i am not able to get the correct
values in the worksheet. The formula that i have give is as follows:

SUM(IF('FIRST'!$E$8:$E$4987="PRODUCT",
IF('FIRST'!$P$8:$P$4987="ANALYSIS",1,0)))

In this Formula, "First" is a work sheet and in the that sheet we
are filtering cells under PRODUCT , and from those PRODUCT cells which
has value ANALYSIS. But the final result that we are getting is not
correct "#Value#'. Can anyone help me in solving this problem.

Regard,
Rajeev Prabhu


--
rajeevprabhuat
------------------------------------------------------------------------
rajeevprabhuat's Profile:
http://www.excelforum.com/member.php...o&userid=25380
View this thread: http://www.excelforum.com/showthread...hreadid=388579

Hi!

Try this instead:

=SUMPRODUCT(--(FIRST!$E$8:$E$4987="PRODUCT"),--(FIRST!$P$8:$P$4987="ANALYSIS"))

Even better:

A1 = Product
B1 = Analysis

=SUMPRODUCT(--(FIRST!$E$8:$E$4987=A1),--(FIRST!$P$8:$P$4987=B1))

Biff

"rajeevprabhuat"
<rajeevprabhuat.1sgciq_1121836049.5538@excelforu m-nospam.com wrote in
message news:rajeevprabhuat.1sgciq_1121836049.5538@excelfo rum-nospam.com...

Hi,

I am working on a worksheet and i am not able to get the correct
values in the worksheet. The formula that i have give is as follows:

SUM(IF('FIRST'!$E$8:$E$4987="PRODUCT",
IF('FIRST'!$P$8:$P$4987="ANALYSIS",1,0)))

In this Formula, "First" is a work sheet and in the that sheet we
are filtering cells under PRODUCT , and from those PRODUCT cells which
has value ANALYSIS. But the final result that we are getting is not
correct "#Value#'. Can anyone help me in solving this problem.

Regard,
Rajeev Prabhu


--
rajeevprabhuat
------------------------------------------------------------------------
rajeevprabhuat's Profile:
http://www.excelforum.com/member.php...o&userid=25380
View this thread: http://www.excelforum.com/showthread...hreadid=388579