How to assign a numeric value to a character and calculate a sum-CodePudding

I'm trying to create a time off tracker in Excel. I'd like to count the frequency of the upper case character V (for a full day of vacation, 1 day) and frequency of the lower case character v (as a half-day of vacation, .5 day).

So, for the range c7:ag7, add the sum of lower v's as .5's and upper v's as 1's

V V v v v (2 uppers and 3 lowers) would equal 3.5 days of vacation.

Can someone recommend a "best" Excel formula or function?

CodePudding user response：

Assuming each cell doesn't contain multiple instances of V and v,

=SUM(IFS(EXACT($C$7:$AG$7,"V"),1,EXACT($C$7:$AG$7,"v"),1/2,TRUE,0))

will do it. (Enter as an array formula prior to Excel 365; i.e. Ctrl Shift Return.)

CodePudding user response：

I worked out this formula and it serves my needs

= SUM(
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"v","")))
 
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"p","")))
 
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"s","")))
 
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"j","")))
 
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"m","")))
 
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"V","")))
 
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"P","")))
 
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"S","")))
 
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"J","")))
 
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"M","")))
)