Sum if specific letter with number

[data808]

Is there a sum if formula if a range of cells has a specific letter with a number? I want to sum the totals for vacation and sick leaves and I will be entering "V8" or "S8" for example. The letter V represents vacation and the letter S is for sick. The number 8 represents the amount of hours they used a specific type of leave so that number could change into quarters as well. Example, V3.25 as they may only need a part of the day to take off for vacation. So basically a range of cells that I type this data into will represent each day of the month and then 2 cells to sum up the vacation and sick totals separately.

Example would be cells A1:AD1 will be the range that I type in V8 or S8 and I would like cell A2 (vacation total) to sum up all the hours of V8s that are typed in the A1:AD1 range. Then cell B2 (sick total) to sum up all the hours for S8 that are typed into the A1:AD1 range.

Is this possible? Thank you so much for the help.

May also need an option for changing V8 to EV8 sometimes for emergency vacation and S8 may change to MA8 for medical appointment. The summing of these changes will still go into the same totals. I just need to be able to add these other options for vacation and sick.

 

[yky]

The following formula works for V8 and others with only one letter.

=SUMPRODUCT((LEFT(A1:AD1,1)="V")*(RIGHT(A1:E1,LEN(A1:AD1)-1)))

 

[data808]

Hi thanks for the reply. The formula did not work for me. What is the A1:E1 before the LEN for? Also would I be able to add an OR function for another criteria instead of just “V”?

The following formula works for V8 and others with only one letter.

=SUMPRODUCT((LEFT(A1:AD1,1)="V")*(RIGHT(A1:E1,LEN(A1:AD1)-1)))

 

[Scott Huish]

How about:

Book1
	A	B	C	D	E	F	G
1	S4	V8	S5	V7
2
3
4	Sick	9
5	Vacation	15
Sheet1

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT(--(MID($A$1:$D$1,2,2)),--(LEFT($A$1:$D$1)="S"))
B5	B5	=SUMPRODUCT(--(MID($A$1:$D$1,2,2)),--(LEFT($A$1:$D$1)="V"))

 

[Scott Huish]

What are your other criteria?

 

[data808]

Thanks this is actually working. I noticed without data entered I get a VALUE error in the cell thats supposed to sum the total. Can that be remedied? Also need a decimal to sum quarters like 4.25 hours if they only use partial day.

Other criteria would be to do an OR function in case I have to type in MA8 for medical appointments. This would still go under the same total for sick leave. Then there is EV8 for emergency vacation which would also be summed up in the vacation totals.

Thanks again for the help. This is getting interesting.

What are your other criteria?

 

[yky]

Hi thanks for the reply. The formula did not work for me. What is the A1:E1 before the LEN for? Also would I be able to add an OR function for another criteria instead of just “V”?

I tested it on A1:E1 and forgot to replace it with AD1 when posting.