Adding LEFT to SUMPRODUCT OFFSET formula in a scrolling calendar

[Nanaia]

I am trying to modify an existing working formula to have it SUMIF (I think) the the 1st character in the cell meets the criteria. This existing formula searches the entire row in a scrolling calendar and counts the number of cells that have the letter located in cell NO$5 (which is "V"). This is used for counting the number of occurrences a vacation day is used. My employer is breaking vacation time down to be available by the hour instead of having to take a full or half day. They now want to track the number of hours as well as the number of days those hours were taken. So if people take 2 hours vacation on Monday and 4 hours of vacation on Friday they have used 6 hours of vacation on 2 days. The calendar spans from column B to column NI, in case that information is needed.

The formula below would be my base formula. All it counts is the number of days - but not if there is a number used after the letter code. What I'd like for them to be able to do is use the code for the vacation (V), paid time off (P), or sick (S) (the letter code is what is in cell NO5), and enter the number of hours was used. For example, if we use "V2" it would be two hours of vacation used.

I thought I could use LEFT to identify the type of time used by looking at the first character in the cell and also to have Excel total the numerical value next to the letter. I am not familiar with the OFFSET function though so I am unsure how to modify the formula to count the days. I don't know how to add a SUMIF function to this formula. I figured (hoped?) once I figured out how to use SUMIF in this formula then I could tweak it creating a new formula in a new cell to calculate the accumulated hours.

=SUMPRODUCT((OFFSET($A8,0,1,1,372)<>"")*(OFFSET($A$3,0,1,1,372))*((OFFSET($A8,0,1,1,372))=NO$5))

 

[JGordon11]

If the V always has a number after it you can use the first formula. If there can be just a V then need to assign a default hours to it as in the second formula where I used 8.

aFilterR1.xlsm
	A	B	C	D	E	F	G	H	I	J
8	38	V5	P	V6	S	V7	V8	V12
9	38	V5	P	V6	S	V7	V	V12
Sheet 8

Cell Formulas
Range	Formula
A8	A8	=LET(r,B8:NI8,SUM(IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1))))
A9	A9	=LET(r,B9:NI9,SUM(IF(r="V",8,IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1)))))

 

[Nanaia]

If the V always has a number after it you can use the first formula. If there can be just a V then need to assign a default hours to it as in the second formula where I used 8.

aFilterR1.xlsm
	A	B	C	D	E	F	G	H	I	J
8	38	V5	P	V6	S	V7	V8	V12
9	38	V5	P	V6	S	V7	V	V12
Sheet 8

Cell Formulas
Range	Formula
A8	A8	=LET(r,B8:NI8,SUM(IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1))))
A9	A9	=LET(r,B9:NI9,SUM(IF(r="V",8,IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1)))))

@JGordon11 Thank you. There will always be a numerical value after the letter code because they want to indicate the code and the number of hours used. Your formula works for totaling the number of hours. How would I count the number of occurrences? I also need to count how many days, not the hours from each day, just the number of days there was an occurance. COUNTIF gives me a #SPILL! error.

 

[Nanaia]

I think I got it...

=SUMPRODUCT(LEN(B8:NI8)-LEN(SUBSTITUTE(B8:NI8,"L","")))

 

[JGordon11]

for count of items that start with "V" in A8:NI8 use:

=SUM(--(LEFT(B8:NI8,1)="V"))