Excel If then Else function in Worksheet
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 |
All times are GMT +1. The time now is 09:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com