|
Syntax of formula
Hi,
I want to set formula.I am trying like this For i = intFrom To intTo If ActiveSheet.Range(strModule & i).Text < "" Then ActiveSheet.Range(strFrom & i).Formula = "=SUM(IF(C17:C2000 = """ & ActiveSheet.Range(strModule & i).Text & """,IF(J17:J2000 = "Fail" ,1,0)))" Else ActiveSheet.Range(strFrom & i).Value = 0 End If Next i I am getting error while I am using IF(J17:J2000 = "Fail",1,0) .What would be the exact syntax. And the other thing in this way I want get the sum of Fail where date is like {=SUM(IF(M17:M2000 = "12/2/2006",IF(J17:J2000 = "Fail",1,0),0))} It also does not give the perfect result. Plz.. help me in this matter Regards, Nil |
Syntax of formula
For i = intFrom To intTo
If ActiveSheet.Range(strModule & i).Text < "" Then ActiveSheet.Range(strFrom & i).Formula = _ "=SUMPRODUCT(--(C17:C2000=""" & ActiveSheet.Range(strModule & i).Text & _ """),--(J17:J2000=""Fail""))" Else ActiveSheet.Range(strFrom & i).Value = 0 End If Next i -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message oups.com... Hi, I want to set formula.I am trying like this For i = intFrom To intTo If ActiveSheet.Range(strModule & i).Text < "" Then ActiveSheet.Range(strFrom & i).Formula = "=SUM(IF(C17:C2000 = """ & ActiveSheet.Range(strModule & i).Text & """,IF(J17:J2000 = "Fail" ,1,0)))" Else ActiveSheet.Range(strFrom & i).Value = 0 End If Next i I am getting error while I am using IF(J17:J2000 = "Fail",1,0) .What would be the exact syntax. And the other thing in this way I want get the sum of Fail where date is like {=SUM(IF(M17:M2000 = "12/2/2006",IF(J17:J2000 = "Fail",1,0),0))} It also does not give the perfect result. Plz.. help me in this matter Regards, Nil |
Syntax of formula
Bob Phillips wrote: For i = intFrom To intTo If ActiveSheet.Range(strModule & i).Text < "" Then ActiveSheet.Range(strFrom & i).Formula = _ "=SUMPRODUCT(--(C17:C2000=""" & ActiveSheet.Range(strModule & i).Text & _ """),--(J17:J2000=""Fail""))" Else ActiveSheet.Range(strFrom & i).Value = 0 End If Next i -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message oups.com... Hi, I want to set formula.I am trying like this For i = intFrom To intTo If ActiveSheet.Range(strModule & i).Text < "" Then ActiveSheet.Range(strFrom & i).Formula = "=SUM(IF(C17:C2000 = """ & ActiveSheet.Range(strModule & i).Text & """,IF(J17:J2000 = "Fail" ,1,0)))" Else ActiveSheet.Range(strFrom & i).Value = 0 End If Next i I am getting error while I am using IF(J17:J2000 = "Fail",1,0) .What would be the exact syntax. And the other thing in this way I want get the sum of Fail where date is like {=SUM(IF(M17:M2000 = "12/2/2006",IF(J17:J2000 = "Fail",1,0),0))} It also does not give the perfect result. Plz.. help me in this matter Regards, Nil Thanks Bob,It works perfectly Regards, Nil |
| All times are GMT +1. The time now is 01:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com