You can try not using a range:
=SUM(IF(RIGHT(A1,1)="*",1,0),IF(RIGHT(A2,1)="*",1,0),IF(RIGHT(A3,1)="*",1,0),IF(RIGHT(A4,1)="*",1,0),IF(RIGHT(A5,1)="*",1,0))
or, you can place the individual =IF() statements into column B (or anywhere else, like column Z, or even into a different worksheet), to...