-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Array formulas
- Thread starter waynered
- Start date
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
waynered said:The array formula below works fine unless one of the cells in the range is blank. The data in the cells I am trying to add is in text format e.g. T.5, F.2 etc.
=IF(LEN(G4:G12)=3,SUM(VALUE(RIGHT(G4:G12,2))),"")
Any help appreciated.
Wayne
{=SUM(IF(LEN(G4:G12)=3,--RIGHT(G4:G12,2)))}
or, the ordinarily entered...
=SUMPRODUCT(--(RIGHT("00"&G4:G12,2))
Upvote
0
Aladin Akyurek said:waynered said:The array formula below works fine unless one of the cells in the range is blank. The data in the cells I am trying to add is in text format e.g. T.5, F.2 etc.
=IF(LEN(G4:G12)=3,SUM(VALUE(RIGHT(G4:G12,2))),"")
Any help appreciated.
Wayne
or, the ordinarily entered...
=SUMPRODUCT(--(RIGHT("00"&G4:G12,2))
What if len>3?
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
jimboy said:Aladin Akyurek said:waynered said:The array formula below works fine unless one of the cells in the range is blank. The data in the cells I am trying to add is in text format e.g. T.5, F.2 etc.
=IF(LEN(G4:G12)=3,SUM(VALUE(RIGHT(G4:G12,2))),"")
Any help appreciated.
Wayne
or, the ordinarily entered...
=SUMPRODUCT(--(RIGHT("00"&G4:G12,2))
What if len>3?
=SUMPRODUCT(--(LEFT(REPLACE(G4:G12,1,1,"")&"00",2)))
Upvote
0
Similar threads
