Sum part of cell if cell contains certain text

[Lunatic90]

Hello,

I have a question:

In a file i have a table with names (column A) and dates (row 1).
In the table i keep track of the holiday requests of my employees.
I do that by noting down the type (A or V) and the number of hours they request in one cell.
The number of hours can be any value between 0 and 8.
For Example, i have V8 for 8 hours of 'verlof'.

Now, i'm wondering if there is a formula that can sum the number of hours within a certain time period (changeable) if the cell starts with V or with A.

See the link for an example picture:
https://imgur.com/kcU8AuZ

In short: i need formulas in the yellow cells that automatically give the same results as i manually calculated.

Is this possible?

Thanks in advance!

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1		1-7	2-7	3-7	4-7	5-7	6-7	7-7	8-7	9-7	10-7	11-7	12-7	13-7	14-7	15-7	16-7	17-7	18-7	19-7	20-7	21-7	22-7	23-7	24-7	25-7	26-7	27-7	Sum V	Sum A
2	William				V8	V8	A4						A4						V8	A4									16	12
3	Dmitri						V5.2	V6.4								V8	V8							V6.4					27.6	0
4	Linda		A4											A4											V8	V8	V8		0	4
5	Sandra																		V8	V8	V8	V8	V8						24	0
6
7			Start Date			05/07/2018
8			End Date			20/07/2018
Sheet1

Cell Formulas
Range	Formula
AC2	=SUMPRODUCT(($B$1:$AB$1>=$F$7)*($B$1:$AB$1<=$F$8)*(LEFT($B2:$AB2,1)=RIGHT(AC$1,1)),("0"&MID($B2:$AB2,2,8))+0)
AD2	=SUMPRODUCT(($B$1:$AB$1>=$F$7)*($B$1:$AB$1<=$F$8)*(LEFT($B2:$AB2,1)=RIGHT(AD$1,1)),("0"&MID($B2:$AB2,2,8))+0)

WBD

 

[Lunatic90]

I think i understand most of the formula, but translating it to my Dutch Excel formulas doesn't seem to work.
I also tried entering it as a Matrix Formula with ctrl+shift+enter, since it looks like this is necessary.

Do you mind explaining the following piece of code?:
I don't really get this part...

[COLOR=Blue]([COLOR=Red]"0"&MID([COLOR=Green]$B2:$AB2,2,8[/COLOR])[/COLOR])+0[/COLOR]

And I use Excel 2013, in case that may cause a problem...

 

[wideboydixon]

It doesn't need Ctrl+Shift+Enter as it's not an array formula. In Dutch my guess is it would be:

=SUMPRODUCT(($B$1:$AB$1>=$F$7)*($B$1:$AB$1<=$F$8)*(LEFT($B2:$AB2,1)="V");("0"&MID($B2:$AB2,2,8))+0)

This piece:

("0"&MID($B2:$AB2,2,8))+0

Converts each line to an array of numbers. You can use the Evaluate Formula button to step through and see where it's failing.

WBD

 

[Lunatic90]

Oh, i'm sorry!
I made an error with the location of brackets near the +0 at the end...

Your formula works like a charm! Thanks!